SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

readjusting month (and year) based on the day

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

readjusting month (and year) based on the day

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!


Accepted Solutions
Solution
‎01-30-2015 10:45 PM
Super User
Super User
Posts: 6,502

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

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;

View solution in original post


All Replies
Super User
Posts: 17,840

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

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


Super Contributor
Posts: 413

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

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

Trusted Advisor
Posts: 1,131

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

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

Thanks,
Jag
Super Contributor
Posts: 413

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

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!

Solution
‎01-30-2015 10:45 PM
Super User
Super User
Posts: 6,502

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

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;

Super Contributor
Posts: 413

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 504 views
  • 7 likes
  • 4 in conversation