Computing a Date Column

[CesarOmarHR]

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

[Linlin]

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]

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

[art297]

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 · Accepted solution]

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]

Thanks Doc, i'll try that out.