using proc sql to select by date one month back

Reply
Occasional Contributor
Posts: 14

using proc sql to select by date one month back

Hey all, I'm finding myself hitting a brick wall here and I'm sure it's silly. 

proc sql;

create table recentapplication as

select * from fulltable where

.entered_date >  intnx ( "month" , today() , -1 )  ;

It's returning all entries.

If I change it to entered_date > today() - 30 that works though  I'm looking to do it from the beginning of previous month whenever I run it.

I'm sure this is just a stupid thing on my part but I'm slowly losing my sanity.  Any help would be greatly appreciated.

Thanks,

-Jeff

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: using proc sql to select by date one month back

That's odd, the following program works fine for me.  note there is a typo in your code - dot before the entered_date.  Are you also sure the entered_date is a date variable?  Perhaps post test data where this fails?

data fulltable;
  entered_date="03MAR2014"d;
  output;
  entered_date="12NOV2014"d;
  output;
run;

proc sql;
  create table RECENTAPPLICATION as
  select  *
  from    FULLTABLE
  where   ENTERED_DATE > intnx("month",today(),-1);
quit;

Occasional Contributor
Posts: 14

Re: using proc sql to select by date one month back

And that was it.  The entered_date was a datetime.  Just needed to put in a datepart() and it took care of it.  Thanks so much for the help!

Ask a Question
Discussion stats
  • 2 replies
  • 1197 views
  • 3 likes
  • 2 in conversation