Solved
Contributor
Posts: 36

# Computing a Date Column

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?

Cesar Hernandez

Accepted Solutions
Solution
‎10-30-2012 11:49 AM
Posts: 2,127

## Re: Computing a Date Column

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

All Replies
Super Contributor
Posts: 1,636

## Re: Computing a Date Column

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;

Contributor
Posts: 36

## Re: Computing a Date Column

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

Super User
Posts: 8,213

## Re: Computing a Date Column

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.

Solution
‎10-30-2012 11:49 AM
Posts: 2,127

## Re: Computing a Date Column

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

Contributor
Posts: 36

## Re: Computing a Date Column

Thanks Doc, i'll try that out.

🔒 This topic is solved and locked.