topic Re: quarter dates in SAS Procedures

quarter dates

hdg — Wed, 18 Apr 2012 17:54:07 GMT

Hi,

I am looking to populate a dataset with the next quarters upto todays date, the quarter date populated should be the last Friday of that quarter

%let mydate = "25Mar2011"d

Output

date

25 Mar 2011

24 Jun 2011

30 Dec 2011

30 Mar 2012

Thanks for all your help.

Re: quarter dates

Haikuo — Wed, 18 Apr 2012 18:25:32 GMT

Try this one, the functions are simple, but looks complicated when being nested:

%let mydate = "25Mar2011"d

;

data want;

format date date9.;

retain date 0;

do _n_=0 by 1 while (date <= intnx('qtr',today(),-1,'s'));

date=ifn(weekday(&mydate)=5, &mydate, intnx('week.7',intnx('qtr',&mydate,_n_,'e'),-1,'e'));

output;

end;

run;

proc print;run;

Haikuo

Re: quarter dates

data_null__ — Wed, 18 Apr 2012 18:32:24 GMT

I don't think it can be done with just one INTNX. I find the last day of interval and then find the Friday by incrementing the interval by 0 using a shifted WEEK interval. Of course the functions could be nested into one statement.

data _null_;

s="25Mar2011"d;

do i=0 by 1;

q = intnx('qtr',s,i,'e');

f = intnx('week.6',q,0); *last Friday of quarter;

if f gt today() then leave;

put (q f) (weekdate. +3);

end;

run;

Re: quarter dates

hdg — Wed, 18 Apr 2012 18:42:03 GMT

Sorry for not being more clear in my example, the quarter should depend on your starting date. I have been getting confused with the qrtr/intnx command. Since it should not just be calendar year quarters.

So say Feb 2012

would be May 2012

etc

Re: quarter dates

Haikuo — Wed, 18 Apr 2012 19:00:14 GMT

Actually what you have just said makes me confused. You need to define the start and end day of each quarter. say for 05Feb2012, is 1feb2012 being the start day and 31may2012 being the end day or 05feb2012 being the start day and 05may2012 being the end day?

Thanks,

Haikuo

Re: quarter dates

hdg — Wed, 18 Apr 2012 19:15:57 GMT

Sorry Haikuo and thanks for all your help! Maybe a worked example will make it more clear

%let mydate = "25Nov2011"d -- this is last friday of november

Output dataset alldates

date

25Nov2011

24Feb2012

Another example ie previous one

%let mydate = "25Mar2011"d

Output dataset alldates

date

25 Mar 2011

24 Jun 2011

30 Dec 2011

30 Mar 2012

%let mydate =

Re: quarter dates

Haikuo — Wed, 18 Apr 2012 19:26:47 GMT

A minor tweak on my previous code should do:

%let mydate = "25Nov2011"d

;

data want;

format date date9.;

retain date 0;

do _n_=0 by 1 while (date <= intnx('qtr',today(),-1,'s'));

date=ifn(weekday(&mydate)=5, &mydate, intnx('week.7',intnx('month',&mydate,3*_n_,'e'),-1,'e'));

output;

end;

run;

proc print;run;

Good Luck!

Haikuo

Re: quarter dates

Linlin — Wed, 18 Apr 2012 19:39:58 GMT

%let mydate = "25mar2011"d;

data want;

format date date9.;

retain date 0;

do _n_=0 by 3 while (date <= intnx('qtr',today(),-1,'s'));

date=intnx('month',&mydate,_n_,'e');

week= intnx('week.6', date,0);

format week date9.;

output;

end;

proc print;run;

Re: quarter dates

Ksharp — Thu, 19 Apr 2012 04:26:12 GMT

How about:

%let mydate = "25nov2011"d;
data want(keep=date);
date=&mydate;output; 
   do until(_d gt "20apr2012"d);
    _d = intnx('month',date,3,'e'); 
    date = intnx('week.6',_d,0,'b'); *last Friday of quarter;
    if date le "20apr2012"d then output;
   end;
format date date9.;
run;

Ksharp