BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Reeza
Super User

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
Barite | Level 11

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
Amethyst | Level 16

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
ilikesas
Barite | Level 11

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
Super User Tom
Super User

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
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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