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

I would like to generate a date column that aproximate date values to a fixed day limit.

If i have 02AUG2011 i would need 31AUG2011 (For days between 1 and 14 the desired date is the 30th/31th)

For 15SEP2012 i would need to recoded that value with 15OCT2012 (For days between 15 and the last day of the month the desired date is the next 15th)

How do i create a recoded column with those specifications?

I would appreciate your help

Cesar Hernandez

1 ACCEPTED SOLUTION

Accepted Solutions
Doc_Duke
Rhodochrosite | Level 12

I think that Cesar is looking for how to do this in the Query Builder.

The data transformation

ifn(day(date)<15,intnx('month',date,0,'e'),intnx('month',date,1,'m'))

could be entered into the "advanced expression" in creating a new column in the Query Builder and then the format applied on the next screen.

Doc Muhlbaier

Duke

View solution in original post

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

try the code below:

data have;

input date mmddyy10.;

cards;

08/01/2012

08/15/2012

08/20/2012

;

data want;

set have;

new_date=ifn(day(date)<15,intnx('month',date,0,'e'),intnx('month',date,1,'m'));

format New_date date date9.;

proc print;run;

CesarOmarHR
Calcite | Level 5

Where do i type this code? In a recoded column option, advance expression or where?

art297
Opal | Level 21

As long as you add a run statement at the bottom of Linlin's 2nd datastep (i.e.:

data want;

  set have;

  new_date=ifn(day(date)<15,intnx('month',date,0,'e'),intnx('month',date,1,'m'));

  format New_date date date9.;

run;

both datasteps could be entered and run by selecting program.

Doc_Duke
Rhodochrosite | Level 12

I think that Cesar is looking for how to do this in the Query Builder.

The data transformation

ifn(day(date)<15,intnx('month',date,0,'e'),intnx('month',date,1,'m'))

could be entered into the "advanced expression" in creating a new column in the Query Builder and then the format applied on the next screen.

Doc Muhlbaier

Duke

CesarOmarHR
Calcite | Level 5

Thanks Doc, i'll try that out.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 945 views
  • 6 likes
  • 4 in conversation