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

find all quarters (in form YYMM) between two dates

Ronein — Sun, 08 Jul 2018 07:06:35 GMT

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;

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

mkeintz — Sun, 08 Jul 2018 08:29:06 GMT

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

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

Ronein — Sun, 08 Jul 2018 08:38:59 GMT

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

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

PaigeMiller — Sun, 08 Jul 2018 14:14:17 GMT

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.

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

Tom — Sun, 08 Jul 2018 14:15:28 GMT

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