topic Re: Macro Variable with list of dates in SAS Programming

Macro Variable with list of dates

tedway — Mon, 15 Apr 2019 13:40:41 GMT

How do I create a macro variable with a list of dates that I can then use in the where statement in proc sql?

This example works with a single date:

data data;
INFILE DATALINES DLM='|';
Input CODE: 3. enddate:yymmdd10.;
datalines;
10|20170905
11|20171031
12|20171103
13|20171204
;
run;

%let datevar0 = 20171031;
%let datevar=%sysfunc(inputn(&datevar0,yymmdd10.), date9.);

proc sql outobs=;
select code, enddate format=yymmdd10. from data
where enddate = "&datevar"d
;
quit;

What I want to so is create a variable with multiple dates like this:

%let datevar = (20171031,20171204);

...and then use proc sql like this (this doesn't work but hopefully it illustrates what I'm trying to accomplish):

proc sql outobs=;
select code, enddate format=yymmdd10. from data
where enddate in ("&datevar"d)
;
quit;

Re: Macro Variable with list of dates

andreas_lds — Mon, 15 Apr 2019 13:50:08 GMT

You need to create date-literals for each value in the list, e.g. with proc sql:

proc sql noprint;
    select cat(quote(put(enddate, date9.)), 'd') 
        into :dateList separated by ',' 
        from data;
quit;

%put &=dateList;

Re: Macro Variable with list of dates

gamotte — Mon, 15 Apr 2019 13:58:13 GMT

Hello,

%let datevar =20171031,20171204;

proc sql outobs=;
select code, enddate format=yymmdd10.
from data
where findw("&datevar.", put(enddate,yymmddn8.),",")
;
quit;

Re: Macro Variable with list of dates

Tom — Mon, 15 Apr 2019 14:44:38 GMT

First a couple of points.

It is much easier in SAS to use space delimited lists. Commas as the delimiter will make coding much harder. So if your values contain spaces then use some other character that does not appear in the data like a pipe | or a caret ^ as the delimiter.
The strings you have are not yet dates. They might look like dates to you but they don't look like dates to SAS.

Can you start with real date values? Either date literals like "31OCT2017"D, or the actual number of days like 21123. If so then it is easy.

%let datevar = 21123 "04DEC2017"d;
proc print data=data;
 where enddate in (&datevar) ;
 var code enddate;
 format enddate yymmdd10.;
run;

If not then convert your original macro variable to a new one that you can use:

%macro convert_ymd2date(list);
%local i;
%do i=1 %to %sysfunc(countw(&list));
 %sysfunc(inputn(%scan(&list,&i),yymmdd10))
%end;
%mend convert_ymd2date;

Then your code can be

%let datevar=20171031 20171204;
proc sql outobs=;
select code
     , enddate format=yymmdd10. 
  from data
  where enddate in (%convert_ymd2date(&datevar))
;
quit;