DATA Step, Macro, Functions and more

find all quarters (in form YYMM) between two dates

Reply
Frequent Contributor
Posts: 147

find all quarters (in form YYMM) between two dates

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;

Trusted Advisor
Posts: 1,346

Re: find all quarters (in form YYMM) between two dates

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_;
Frequent Contributor
Posts: 147

Re: find all quarters (in form YYMM) between two dates

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

 

 

Respected Advisor
Posts: 3,069

Re: find all quarters (in form YYMM) between two dates

[ Edited ]

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.

 

 

--
Paige Miller
Super User
Super User
Posts: 8,129

Re: find all quarters (in form YYMM) between two dates

[ Edited ]

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);

 

Ask a Question
Discussion stats
  • 4 replies
  • 97 views
  • 1 like
  • 4 in conversation