topic Flag when between 2 dates with SQL in SAS Data Management

Flag when between 2 dates with SQL

Miracle — Fri, 23 Nov 2018 20:33:14 GMT

Hi all,

May I know how do I flag my data entries when they are between 2 dates(disregard the time portion) using Sql? The datatype of the condition_start_date column is timestamp with format YYYY-MM-DD HH:MM:SS

Below is what I have tried.

Your help is very much appreciated.

CREATE TABLE incl_crit1 AS
    SELECT *, CASE 
				WHEN condition_start_date BETWEEN "2010-07-01 00:00:00" and "2018-06-30 00:00:00" 
               		THEN 1 
               		ELSE 0 
       			END as incl_crit
    FROM cond_codes1)

Re: Flag when between 2 dates with SQL

Kurt_Bremser — Fri, 23 Nov 2018 20:38:50 GMT

SAS has only two data types, character and numeric. Which of those is condition_start_date?

Your condition would only work with character data.

Re: Flag when between 2 dates with SQL

r_behata — Fri, 23 Nov 2018 21:03:04 GMT

Assuming that condition_start_date is in sas datetime format . You can use the Date constants in the between clause way :

Proc SQl;
CREATE TABLE incl_crit1 AS
    SELECT *, CASE 
				WHEN condition_start_date BETWEEN '01jul2019 00:00:00'dt and '30jun2018 00:00:00'dt
               		THEN 1 
               		ELSE 0 
       			END as incl_crit
    FROM cond_codes1;
Quit;

Re: Flag when between 2 dates with SQL

PGStats — Fri, 23 Nov 2018 21:08:22 GMT

If condition_start_date is a SAS datetime (the format wouldn't matter), you could use:

CREATE TABLE incl_crit1 AS
SELECT 
    *, 
    condition_start_date BETWEEN "01JUL2010:00:00:00"dt and "30JUN2018:23:59:59"dt as incl_crit
FROM cond_codes1;

If condition_start_date is character then you could get away with:

CREATE TABLE incl_crit1 AS
SELECT 
    *, 
    substr(condition_start_date,1,10) BETWEEN "2010-07-01" and "2018-06-30"  as incl_crit
FROM cond_codes1;