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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.