Desktop productivity for business analysts and programmers

Computing a Date Column

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

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?

I would appreciate your help

Cesar Hernandez


Accepted Solutions
Solution
‎10-30-2012 11:49 AM
Valued Guide
Posts: 2,111

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

View solution in original post


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?

Esteemed Advisor
Posts: 7,301

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
Valued Guide
Posts: 2,111

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.

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

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