topic Re: editing macro list in New SAS User

editing macro list

judyluu — Sat, 10 Apr 2021 02:06:15 GMT

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!

Re: editing macro list

Tom — Sat, 10 Apr 2021 04:13:52 GMT

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 ;

Re: editing macro list

judyluu — Sat, 10 Apr 2021 05:21:52 GMT

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!

Re: editing macro list

Tom — Sat, 10 Apr 2021 17:50:04 GMT

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.