topic Re: dynamic programming - creating date ranges in SAS Programming

dynamic programming - creating date ranges

avbraga — Tue, 17 Dec 2013 16:58:37 GMT

Hi all,

I'm looking to automate the piece of code below using macros, %sysfuc(), and do loops, but I'm not very well versed using %sysfuc() and would appreciate your help.

I want to be able to set up 2 macro variables (something like: %let start_date = 01Jul2012; %let end_date = 30Aug2013;) and have the intervals bellow be constructed for me automatically. This is the hard code:

**************

data testb;

set testa;

if '01Jul2012'd <=SvcBegDate< '01Aug2012'd then do; monyr="Jul2012" ; order=1; end;

if '01Aug2012'd <=SvcBegDate< '01Sep2012'd then do; monyr="Aug2012" ; order=2; end;

if '01Sep2012'd <=SvcBegDate< '01Oct2012'd then do; monyr="Sep2012" ; order=3; end;

if '01Oct2012'd <=SvcBegDate< '01Nov2012'd then do; monyr="Oct2012" ; order=4; end;

if '01Nov2012'd <=SvcBegDate< '01Dec2012'd then do; monyr="Nov2012" ; order=5; end;

if '01Dec2012'd <=SvcBegDate< '01Jan2013'd then do; monyr="Dec2012" ; order=6; end;

if '01Jan2013'd <=SvcBegDate< '01Feb2013'd then do; monyr="Jan2013" ; order=7; end;

if '01Feb2013'd <=SvcBegDate< '01Mar2013'd then do; monyr="Feb2013" ; order=8; end;

if '01Mar2013'd <=SvcBegDate< '01Apr2013'd then do; monyr="Mar2013" ; order=9; end;

if '01Apr2013'd <=SvcBegDate< '01May2013'd then do; monyr="Apr2013" ; order=10; end;

if '01May2013'd <=SvcBegDate< '01Jun2013'd then do; monyr="May2013" ; order=11; end;

if '01Jun2013'd <=SvcBegDate< '01Jul2013'd then do; monyr="Jun2013" ; order=12; end;

run;

**************

I've been playing around with the code below trying to automate it, but I'm not getting it right:

%let start_date = 01Jul2012;

%let end_date = 30Aug2013;

%let start = %sysfunc(inputn(&start_date.,anydtdte9.));

%let end = %sysfunc(inputn(&end_date.,anydtdte9.));

%let dif = %sysfunc(intck(month,&start.,&end.));

%let date1 = %sysfunc(putn(%sysfunc(intnx(month,&start.,1,b)),date9.));

%let date1a = %sysfunc(intnx(month,&start.,1,b));

%let date_final = %sysfunc(putn(%sysfunc(intnx(month,&start.,2,b)),monyy7.));

data testb;

set testa;

do i = 0 to &dif.;

if %sysfunc(intnx(month,&start.,&i.,b)) <= SvcBegDate < %sysfunc(intnx(month,&start.,&i.+1,b)) then do;

monyr = %sysfunc(putn(%sysfunc(intnx(month,&start.,&i.,b)),monyy7.)));

order = &i.+1;

end;

END;

run;

**************

I would appreciate your help with this. Thanks.

Alex

Re: dynamic programming - creating date ranges

Reeza — Tue, 17 Dec 2013 17:19:53 GMT

If SvcBegDatea is a date in dataset, why do you need all the macro functions to create this?

Here's a non-macro solution.

data test;

set testa;

monyr=put(SvcBegDate, monyy7.);

order=mod(month(SvcBegDate)+5, 12)+1;

run;

Re: dynamic programming - creating date ranges

PGStats — Tue, 17 Dec 2013 17:27:41 GMT

Keep it simple.

%let start_date = 01Jul2012;
%let end_date = 30Aug2013; /* not used */

data testa;
format svcBegDate date9.;
svcBegDate = '05jul2012'd;
run;

data testb;
set testa;
monYr = propcase(put(SvcBegDate, monyy7.));
order = 1 + intck("MONTH", "&start_date."d, svcBegDate);
run;

proc print data=testb noobs; run;

Re: dynamic programming - creating date ranges

avbraga — Tue, 17 Dec 2013 17:31:58 GMT

Hi Reeza,

Thank you for your help. This is good but not quite doing what I would like done. The order variable is grouping monyr by the month, so things like JUL2012 and JUL2013 are getting order=1, when it should be:

for JUL2012, order = 1,

for AUG2012, order = 2,

for SEP2012, order = 3,

..., etc.

Thank you anyway, I liked the simplicity of your thinking.

Re: dynamic programming - creating date ranges

avbraga — Tue, 17 Dec 2013 17:37:02 GMT

That did it, PG. Keeping it simple is good!

Thank you!