topic Re: readjusting month (and year) based on the day in SAS Data Management

readjusting month (and year) based on the day

ilikesas — Sat, 31 Jan 2015 02:34:47 GMT

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!

Re: readjusting month (and year) based on the day

Reeza — Sat, 31 Jan 2015 03:18:53 GMT

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

Re: readjusting month (and year) based on the day

ilikesas — Sat, 31 Jan 2015 03:38:46 GMT

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

Re: readjusting month (and year) based on the day

Jagadishkatam — Sat, 31 Jan 2015 03:44:07 GMT

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

Re: readjusting month (and year) based on the day

Tom — Sat, 31 Jan 2015 03:45:42 GMT

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;

Re: readjusting month (and year) based on the day

ilikesas — Sat, 31 Jan 2015 03:50:49 GMT

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!

Re: readjusting month (and year) based on the day

ilikesas — Sat, 31 Jan 2015 03:52:31 GMT

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