topic Re: create varlist macro varaible with dates in 2 /5/10/15/20/25 in SAS Programming

create varlist macro varaible with dates in 2 /5/10/15/20/25

Ronein — Tue, 31 Dec 2019 13:18:27 GMT

Hello

I have to following problem.

User define 2 parameters:

%let From='14AUG2019'd;

%let Until='16SEP2019'd;

The task is to create another macro variable that will include all dates between &FROM and &until but only 2,5,10,15,20,25 dates.

So in this example a new Varlist macro variable will be created with values:

'15AUG2019'd+'20AUG2019'd+'25AUG2019'd+'02SEP2019'd+'05SEP2019'd+'10SEP2019'd+'15SEP2019'd;

Another Example:

%let From='24OCT2019'd;
%let Until='17NOV2019'd;

So in this example a new Varlist macro variable will be created with values:
'25OCT2019'd+'02NOV2019'd+'05NOV2019'd+'10NOV2019'd+'15NOV2019'd;

Then I have another task:

How should I transform value to YYMMDD6 structure

'15AUG2019'd+'20AUG2019'd+'25AUG2019'd+'02SEP2019'd+'05SEP2019'd+'10SEP2019'd+'15SEP2019'd;

will be transformed to

190815+190820+190825+190902+190905+190910+190915;

Re: create varlist macro varaible with dates in 2 /5/10/15/20/25

Tom — Tue, 31 Dec 2019 14:08:55 GMT

Note that there is a limit to how many characters you can put into a single macro variable.

It is probably easier to do this in two steps. One to make the list and another to make the macro variable(s).

%let From='24OCT2019'd;
%let Until='17NOV2019'd;

data list ;
  do date=&from to &until ;
    if day(date) in (2 5 10 15 20 25) then output;
  end;
run;

proc sql noprint;
  select cats(quote(put(date,date9.)),'d') 
       , put(date,yymmdd6.)
    into :varlist1 separated by '+'
       , :varlist2 separated by '+'
    from list
  ;
quit;

398   %put &=varlist1;
VARLIST1="25OCT2019"d+"02NOV2019"d+"05NOV2019"d+"10NOV2019"d+"15NOV2019"d
399   %put &=varlist2;
VARLIST2=191025+191102+191105+191110+191115

Re: create varlist macro varaible with dates in 2 /5/10/15/20/25

unison — Tue, 31 Dec 2019 14:13:59 GMT

I'd take this approach:

data want;
format mydate date9.;
do i='14AUG2019'd to '16SEP2019'd;
	mydate=i;
	if day(mydate) in (2,5,10,15,20,25) then output;
end;
drop i;
run;

proc sql noprint;
select "'"||put(mydate,date9.)||"'d" into :maclist1 separated by "+"
from want
;quit;

%put &maclist1;

proc sql noprint;
select put(mydate,yymmn6.) into :maclist2 separated by "+"
from want
;quit;

%put &maclist2;

Re: create varlist macro varaible with dates in 2 /5/10/15/20/25

Ronein — Tue, 31 Dec 2019 14:46:50 GMT

You are the best!