comparing date25.6 in SAS release: 9.04.01M4P11092016

[anunick]

Hi, 

I appreciate your help.

I'm trying to filter out the records on the basis of a certain cutoff date i.e. I wish to create a subset of data after a certain date, let's say : 1st August 2019. 

Here is my code:

data cmarerr;
set bcccparp.bcc_cmar_subm_event_details_err;
where ERROR_TREASON like '%Patient Date of Birth is not valid%' and CREATE_TIMESTAMP > '01AUG2019'd;
run;

It produces No errors or warnings but the second condition of date doesn't seem to work as it doesn't filter out other previous entries.

[Tom]

Why are you comparing a variable with TIMESTAMP in its name to a DATE value instead of a DATETIME value?

You are probably just limiting yourself to dates that are after about 6AM on first day in 1960.

1209  data x ;
1210    x='01AUG2019'd;
1211    put x= datetime24.;
1212  run;

x=01JAN1960:06:02:42

Why not use a datetime literal instead?

'01AUG2019:00:00'dt

[PeterClemmensen]

Welcome to the SAS Community 🙂

I can't see your data, but it seems like CREATE_TIMESTAMP is not a valid SAS date. Find out how this is constructed. If it is a datetime value, then use datepart like this

data cmarerr;
	set bcccparp.bcc_cmar_subm_event_details_err;
	where ERROR_TREASON like '%Patient Date of Birth is not valid%' and datepart(CREATE_TIMESTAMP) > '01AUG2019'd;
run;

[ballardw]

Run Proc Contents on your data set bcccparp.bcc_cmar_subm_event_details_err and show us the type and format associated with your variable CREATE_TIMESTAMP .

The most likely issues I see are that your CREATE_TIMESTAMP may be a datetime variable, which would require

datepart(CREATE_TIMESTAMP) > '01AUG2019'd

or may be a character variable and need conversion to either a date or datetime variable.

[anunick]

Hi,

Thanks for the response.

I checked the column properties:

Length: 8

Type: Numeric

Format: DATETIME25.6

InFormat: DATETIME25.6

E.g. of one of the entries in SAS dataset: 

18AUG2019:01:08:21.000000

Thanks

Anuvrat

[Tom]

So your variable does have datetime values (number of seconds since 1960) and not date values (number of days since 1960).

Just remember to compare to the proper type of number.  So either use datetime literals or use the DATEPART() function to convert the number of seconds your variable has into number of days and then you can compare to date values.