How to create a macro variable from a list of date in a table SAS

Question

I have a work table named WORK.WEEK_YEAR_FESTIVITA with two records with dates 08dec2022 and 09dec2022 in the field "HolidayDate" and I would to convert this list in a macro variable like "Elenco_Date = '08dec2022'd,'09dec2022'd" and so on for other possible dates in the table WORK.WEEK_YEAR_FESTIVITA. I tried with this proc SQL:

proc sql noprint; select distinct HolidayDate into : Elenco_Date separated by "'d," from WORK.WEEK_YEAR_FESTIVITA; quit;

%put &Elenco_Date;

but the result IS:

ELENCO_DATE = 08DEC2022'd,09DEC2022;

and not

ELENCO_DATE = '08DEC2022'd,'09DEC2022'd;

as desired

Do you have suggestions?

Thanks

score 0 · Answer 1 · answered Dec 14 '22 at 13:35

Create the date literal strings in the column list part of the SELECT statement.

select distinct quote(put(HolidayDate,date9.))||'d'
  into :Elenco_Date separated by ',' 
  from WORK.WEEK_YEAR_FESTIVITA
;

Or you could just store the raw number of days into the macro variable.

select distinct HolidayDate format=6.
  into :Elenco_Date separated by ',' 
  from WORK.WEEK_YEAR_FESTIVITA
;

Since there is no difference between using

where date = "08DEC2022"d

and

where date = 22987

How to create a macro variable from a list of date in a table SAS

1 Answers1