dynamic programming - creating date ranges

[avbraga]

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

[Reeza]

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;

[avbraga]

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.

[PGStats · Accepted solution]

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;

PG

[avbraga]

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

Thank you!