CODEWITHSUNDEEP
sassas-macro
heraldic2
heraldic2

Reputation: 3

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

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.

Upvotes: 0

Views: 251

Answers (1)

Tom
Tom

Reputation: 51621

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:

enter image description here

Upvotes: 3

Related Questions