Date Manipulation problem

[evaaseow]

I have a PROC SQL query that goes like the following:

PROC SQL;
SELECT * FROM TABLE1
WHERE START_DATE BETWEEN (beginning of last month) TO (end of last month);
QUIT;

How do I do this? The base date I want to manipulate is sysdate.

Thanks

 

[ChrisW75]

It's BETWEEN ... AND ...

Ye gods, every morning at the station the announcer tells me the train is express between Malvern to South Yarra, and it's so so so wrong, it make me want to scream.
The english language is doomed I tell you. DOOMED!


Oh, and remember, the between ... and ... statement is inclusive of both numbers.

Or were you needing help getting the first and last of last month? That one's a bit more tricky, but it's fun to work out. You need the INTNX function which you can use to get to the first of a month easily enough. You'll possibly need to nest a couple of them to get the correct day, experiment with a couple of lines and you'll get it quickly enough. I have to work it out from scratch each time myself.
Doco on INTNX is here:-

http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000212700.htm

There's also some discussion on the limitations of INTNX in this forum elsewhere.
Let us know if you're still stuck.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[klaz2002]

The sysdate variable that you mention, is that the SAS Sysdate variable? The reason I ask is you don't need that date to get the current date. Perhaps you could do the following.

PROC SQL;
   SELECT * FROM TABLE1
   WHERE (month(today())-1) eq month(START_DATE) and year(today()-31) eq year(start_date);

QUIT;

This presumes that your start_date variable is a SAS date variable and not a datetime variable.

Hope this helps you,
Klaz