readjusting month (and year) based on the day

[ilikesas]

Hi,

suppose I have the following code:

data day;

format date mmddyy10. ;

input date:ddmmyy10. ;

datalines;

2/1/2010

5/6/2010

16/8/2010

;

run;

What I would like to get is the following: if day(date) less than 6 then date1 = date - month(date), else date1 = date.

Like this the new dates that I should get are:

2/12/2009

5/5/2010

16/8/2010

I did the following code:

data day1;

set day;

format date1 ddmmyy10. ;

if day(date) lt 6 then date1 = date - month(date);

else date1 = date;

run;

But the result just made date1 equal date...

Thank you!

[Reeza]

ilikesas wrote:

But the result just made date1 equal date...

If you take a close look, you're not getting the same dates but you're not getting what you want either.

Since SAS dates are numbers when you subtract the 'month' you're subtracting at most 12 days.

It seems like you want to decrement the month variable. You do that via the intnx function. 

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212700.htm

[ilikesas]

Hi Reeza,

thanks for the link, I did the following code:

data day1;

set day;

format date1 ddmmyy10. ;

if day(date) lt 6 then date1 = intnx('month', date, -1, 'same');

else date1 = date;

run;

and the result was like I wanted it

[Jagadishkatam]

You got a better solution than what i am providing, but this is an alternative way without intnx and produce same result as intnx

data day(drop=month2 year month);

format date ddmmyy10. ;

input date:ddmmyy10. ;

if day(date)<6 then month2=month(date)-1;

if .< month2 <=0 then year=year(date)-1;

if .< month2 <=0 then month=13-month(date);

if month2=0 then newd=mdy(month,day(date),year);

else if month2>0 then newd=mdy(month2,day(date),year(date));

else if  month2=. then     newd=mdy(month(date),day(date),year(date));

format newd ddmmyy10.;

datalines;

2/1/2010

2/2/2010

2/12/2010

5/6/2010

16/8/2010

;

run;

Thanks,

Jag

[ilikesas]

thanks for the code, I was actually thinking to do the same in the beginning, but thought that there must be a more "elegant" way without writing so much code, and apparently intnx does just that!

[Tom · Accepted solution]

INTNX() is good for manipulating dates.

data day;

format date1 yymmdd10.;

informat date1 yymmdd10.;

input date1 ;

if day(date1) < 6 then date2 = intnx('month',date1,-1,'s') ;

else date2=date1;

format date2 yymmdd10.;

put (date1 date2) (=);

datalines;

2010/02/01

2010/06/05

2010/08/16

;

run;

[ilikesas]

hi Tom, with Reeza's advice I did pretty much a smaller version of your code