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

Tom · Accepted Answer · 2022-12-14 13:35:13Z

0

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

answered Dec 14, 2022 at 13:35

Tom

52.3k2 gold badges19 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

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

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