All dates 'later than'.... returns wrong results

[osho]

I have a field called end_date with a variable type 'datetime'. example of record is 31MAR2009:00:00:08

I used the following command to get the date part
sas_end_date=put(datepart(date_end),date9.);

the sas_end_date has date in the format 20JAN2009 etc.

Goal: To return only those records which have date later than 29 april 2009. But when I use the code below, it does not give me the required results.

data test2;
set test1;
where sas_end_date gt '29APR2009';
run;

It deletes many records but the resulting data sets have dates from 2004 to 2009 December.

When I use less than

data test2;
set test1;
where sas_end_date lt '29APR2009';
run;


it does not delete a single record.

Useful information: The sas_date_end variable is of type $9 standard character. Why is this the case, shouldn't it be date since I used datepart function above?

[sbb]

SAS DATE and DATETIME type variables are numeric, not character, and so you must use the numeric-version for properly GT/LT testing, instead of using character-format representations. Also, when you want to refer to a SAS date or datetime literal in single- or double-quotation marks, you just append the identifier "D" or "DT" to the literal string that is enclosed in the quotation.

Recommended reading from the SAS support http://support.sas.com/ website on the topic of using SAS DATE and DATETIME variables:

About SAS Date, Time, and Datetime Values
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm

Also, a SAS supplemental technical paper on this topic:

SAS Dates, Times, and Interval Functions
Definitions and Explanations
SAS Dates, Times, and Datetimes:
http://support.sas.com/techsup/technote/ts668.pdf


Scott Barry
SBBWorks, Inc.

[osho]

Thanks for your reply. I had already read the links you sent and tried it.

When I convert the field type to numeric, I used the code below

data test1;
set test;
onedate_end_date=put(datepart(date_end),date9.);
run;

which was fine.

Then I try to find all records that have dates later than 29 April 2009

data test1;
set test1;
final=input(onedate_end,9.); /*convert date field to numeric*/
run;

data test2;
set test1;
where final gt "29APR2009"d;
run;

It deleted all records!
What am I missing here?
Thanks in advance.

[sbb]

Review your SAS Log output closely, with your second DATA step; there should be a message ONE_DATE_END VARIABLE IS UNINITIALIZED.

Check your INPUT function to assign FINAL, and you will find that the INFORMAT being used is incorrect -- there should be a SAS diagnostic related.

So, for your own desk-checking diagnostics and validation consider using SAS FORMAT statements for variables like FINAL, to represent them as a DATE string. Then use PROC PRINT along the way (after the DATA steps), or use SAS PUTLOG commands in the DATA step to see what is being derived for your assignment statement.

Scott Barry
SBBWorks, Inc.

[Doc_Duke]

The core problem is your put element.

onedate_end_date=put(datepart(date_end),date9.);

That creates a character string that looks like a date.

To get a SAS date, use
enddate=datepart(date_end);
format enddate date9.;

Then your where statement would look like

where enddate gt '29APR2009'd;

(note the "d" at the end of the date constant).

Doc

[osho]

Thank you Doc@Duke. I realized my folly and achieved my results.
Many thanks for your time.