SAS Date Functions

1
votes

I have a SAS data set where one of the columns named ISSUE_DATE is text type and has date values in the format of 'YYYYMMDD'. I'm writing a query against the this data set and would like to retrieve all rows where ISSUE_DATE is <= today -2 . How can i write the query for this.

SELECT * FROM WORK.DATASET WHERE ISSUE_DATE <= today()-2.

I'm new to SAS and don't know much date functions and conversions. Any help is greatly appreciated.

Thanks

2
sas
It's probably worth noting that whichever method you use, any missing date values will be returned as they will count as being <=today()-2. If you want to exclude these then add an extra condition - 'and issue_date is not null' – Longfish

2 Answers

6
votes

You need to convert the data type to numeric, using the INPUT function and the relevant informat, for SAS to recognise the date. You can do this within the WHERE clause which saves creating an extra column. So your query will look like :

SELECT * FROM WORK.DATASET WHERE INPUT(ISSUE_DATE,YYMMDD8.) <= today()-2

1
votes

Following up on Keith's answer, it may be more efficient to do this:

SELECT * 
FROM   WORK.DATASET 
WHERE  ISSUE_DATE <= PUT( today()-2 , YYMMDDN8.)

This will save SAS from having to run the INPUT function on each observation.