Use computed macro variable as a new column name in existing table

Question

I am generating a report that does a look back over the past 45ish days. The requestor wants the dates as the column headers so I am trying to write a macro loop that just goes through the dates in reverse order to create the columns. I will then write logic to take that rows ID and that columns date to populate the values needed. However, I am having difficulty getting the date variable I have computed turned into the new column header.

The macro loop works and creates the correct date in the variable in each iteration, but how do I take that and make it a new column in the table?

Desired output is this:

ID	Name	08Nov2022	07Nov2022	06Nov2022
1	Cell 2	0	0	0
2	Cell 4	0	0	0

%LET iDayCount=45;

/* Create a new temp table by selecting the values from a permeanent table housing the category IDs, names and details
Call this temp.parent_table*/


%MACRO test;
     DATA temp.parent_table;
     SET temp.parenet_table;
     %LET today=sysfunc(today));
     %DO iCounter=0 %TO &iDayCount;
          %LET colName=%sysfunc(intnx(day,-&iCounter),date9.);
          /* THIS IS WHERE IT GOES OFF THE RAILS */
          /* I want to use colName value as a new column in the temp.parent_table*/
          &colName = 0;
     %END
     RUN;
%MEND;
%test;

The log has a note for each iteration: NOTE: Line generated by the macro variable "COLNAME". "08NOV2022

Each date in the note is underlined red with the error message: Error 180-322: Statement is not valid or it is used out of proper order

As always your help is appreciated.

Your requested output looks like a report and not a dataset. Note that you cannot name a variable starting with a digit, which is what the error message is telling you. Before trying to use macro code to generate code make sure you can generate the desired code by hand and that it works. — Tom
– Tom, Commented Nov 8, 2022 at 14:53

Tom · Accepted Answer · 2022-11-08 15:02:03Z

3

The easiest way to make a report that has date values as column headers is to use PROC REPORT. Store the date values in a variable and use it as an ACROSS variable in the report.

So if you have data like this:

Obs    ID    Name          date    value

 1      1    Cell2    08NOV2022      1
 2      1    Cell2    07NOV2022      2
 3      1    Cell2    06NOV2022      3
 4      2    Cell4    08NOV2022      4
 5      2    Cell4    07NOV2022      5
 6      2    Cell4    06NOV2022      6

You can make your report using code like this:

proc report ;
  columns id name value,date ;
  define id/group;
  define name/group;
  define value / sum ' ';
  define date / across order=internal descending ' ';
run;

Result:

answered Nov 8, 2022 at 15:02

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use computed macro variable as a new column name in existing table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related