Hello
User define two parameters :
%let start=1501; /*Jan 2015*/
%let end=1807;/*July 2018*/
The task is to find all quarters (in form YYMM) between start and end .
In this example we need to get:
1503 (t1)
1506 (t2)
1509 (t3)
1512 (t4)
1603 (t5)
1606 (t6)
1609 (t7)
1612 (t8)
1703 (t9)
1706 (t10)
1709 (t11)
1712 (t12)
1803 (t13)
1806 (t14)
May anyone suggest how to do it please?
I wrote this code but I didn't get the correct answer
%let start=1501;
%let end=1807;
%let date_start=%sysfunc(inputn(&start.,yymmn4.));
%let date_end=%sysfunc(inputn(&end.,yymmn4.));
%put &date_start.;
%put &date_end.;
/*sas number of quarters between two dates*/
Data _null_;
no_of_quarters=intck('QUARTER', &date_start., &date_end.);
Call Symput("n",no_of_quarters);
/*call symput("n",trim(left(no_of_quarters)));*/
Run;
%put &n.;
%macro months;
%do j=1 %to &n.;
t&j.=put(intnx('QUARTER',&date_start.,&j.),yymmn4.);
call symput("t&j",trim(left(t&j.)));
%end;
%mend;
data _null_;
%months;
run;
It's probably a lot neater to do the work in a data _null_ step and use call symput to establish the macrovars:
%let b=1501;
%let e=1807;
data _null_;
bdate=mdy(mod(&b,100),1,floor(&b/100));
edate=mdy(mod(&e,100),1,floor(&e/100));
do date=intnx('qtr',bdate,0,'end') by 0 while (date<edate);
t=sum(t,1);
call symput(cats('T',t),trim(put(date,yymmn4.)));
date=intnx('qtr',date,1,'end');
end;
run;
%put _user_;
Sorry,Maybe I didn't explain it well
We need to create parameters which are called t1 ,t2,t3..etc.
They will get following values
t1=1503
t2=1506
t3=1509
t4=1512
....
t14=1806
The code provided by @mkeintz seems to do what you want. If it does not provide what you want, explain further. What happens when you run the code? Do you get an error? Does it produce the wrong results? Explain!
Also, from now on, when you are showing us your code, please click on the running man icon and paste your code into the window that opens.
You don't explain how your attempt failed but it looks like your approach is nearly correct. Your algorithm finds the start of the quarter. But it looks from your required output that you want to find the END of the PREVIOUS quarter.
It is probably easier to do it all in a data step instead of mixing data steps and macro.
%let start=1501; /*Jan 2015*/
%let end=1807;/*July 2018*/
data _null_;
start = input("&start",yymmn.);
end = input("&end",yymmn.);
do t=1 to intck('qtr',start,end);
length varname $32 value $10 ;
varname = cats('T',t) ;
value = put(intnx('qtr',start,t-1,'e'),yymmn4.) ;
put varname= value= ;
call symputx(varname,value);
end;
run;
You could also do it all in macro code. If you make a separate macro and want to use the generated macro variables then you need to make them GLOBAL so that they will continue to exist after the macro finishes.
%macro make_t(start,end);
%local t varname ;
%let start=%sysfunc(inputn(&start,yymmn));
%let end=%sysfunc(inputn(&end,yymmn));
%do t=1 %to %sysfunc(intck(qtr,&start,&end)) ;
%let varname=T&t ;
%global &varname ;
%let &varname = %sysfunc(intnx(qtr,&start,&t-1,e),yymmn4) ;
%put &varname = &&&varname ;
%end ;
%mend make_t ;
%let start=1501; /*Jan 2015*/
%let end=1807;/*July 2018*/
%make_t(&start,&end);
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.