Macro Variable with list of dates

[tedway]

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;

[andreas_lds]

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;

[gamotte]

Hello,

%let datevar =20171031,20171204;

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

[Tom · Accepted solution]

First a couple of points.

1. 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.
2.  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;