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
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
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;
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.
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
That did it, PG. Keeping it simple is good!
Thank you!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.