topic Re: using proc sql to select by date one month back in SAS Programming

using proc sql to select by date one month back

JeffU — Tue, 11 Nov 2014 16:31:57 GMT

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

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

RW9 — Tue, 11 Nov 2014 16:43:17 GMT

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;

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

JeffU — Tue, 11 Nov 2014 17:52:08 GMT

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!