editing macro list

[judyluu]

Below I have the results of the macros that I have created. 

%put &alldates1.;

'02/22/2021', '02/23/2021', '02/25/2021', '02/26/2021', '03/01/2021'

%put &alldates2.;
'02/22/2021' '02/23/2021' '02/25/2021' '02/26/2021' '03/01/2021'

I want to make edits to the macros to look like this without having to type them out or make edits to each item on the list:

%put &alldates1.;

'02/22/2021'N, '02/23/2021'N, '02/25/2021'N, '02/26/2021'N, '03/01/2021'N

%put &alldates2.;

'02/22/2021'N '02/23/2021'N '02/25/2021'N '02/26/2021'N '03/01/2021'N

Thanks in advance!

[Tom · Accepted solution]

It would probably be easier to generate the N when you make the macro variables.

But a call to TRANWRD() should do it.

%let have='02/22/2021', '02/23/2021', '02/25/2021', '02/26/2021', '03/01/2021';
%let want=%sysfunc(tranwrd(%superq(have),%str(,),%str(N,)))N ;

[judyluu]

works perfectly! 

For learning purposes I was wondering how to have it in the list before the macro is created.

proc sql;
select distinct quote(strip(DueDate)," ' ")
into: alldates1 separated by ','
from Pivot1;
quit;

This is what I did to create the macro list. I was able to figure out how to add the quotes, but I am not too sure how to add the "N".

Thanks again!

[Tom]

Just include in the column you are selecting into the macro variable. Also add NOPRINT so your listing is not cluttered with the list of values.

proc sql noprint
select distinct cats(quote(strip(DueDate),"'"),'N')
  into :alldates1 separated by ','
  from Pivot1
;
quit;

Are you really trying to create a dataset with variable names that look like date strings? You could also use the NLITERAL() function to add the quotes and N when needed.

nliteral(DueDate)

Of if DueDate really has some leading spaces you want removed:

nliteral(left(DueDate))

Why not just keep the dataset in a natural format and produce such a "table" as a REPORT and not a DATASET.  PROC REPORT or PROC TABULATE can easily use the value of DATE variable as the header for a column in the report it produces.