CODEWITHSUNDEEP
datesasconstantsrange
keherder
keherder

Reputation: 65

Invalid date constant: Trying to subset data into date range

I am a novice (and I mean NOVICE) SAS user. I am trying to create a new table within a certain date range, but I am confused why it won't work for me.

I have a column doi (date of incident) in YYYY-MM-DD format. I am trying to create a new table only within a date range, so I've put:

if "2020-01-01" <= doi =< "2020-03-29"

But the error message said "Invalid date/time/datetime contant". What can I do?

Thank you so much! SAS has been a struggle for me...

EDIT: Here was my original code:

data want; 
set sasdata.have

; if "2020-01-01"d <= doi =< "2020-03-29"d; run;

I just want to make a new table with all the variables within that date range. Thank you!!

Upvotes: 0

Views: 1521

Answers (2)

Tom
Tom

Reputation: 51611

You need to compare numbers (dates are stored as number of days) to numbers instead of strings. If you do include strings like in your example then SAS will try to convert them into numbers using the normal numeric informat which does not understand what to do with the hyphens. So your test is just testing if DOI is missing.

447   data test;
448     format doi yymmdd10.;
449     if "2020-01-01" <= doi <= "2020-03-29" then put 'YES';
450   run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      449:6    449:29
NOTE: Variable doi is uninitialized.
NOTE: Invalid numeric data, '2020-01-01' , at line 449 column 6.
NOTE: Invalid numeric data, '2020-03-29' , at line 449 column 29.
YES
doi=. _ERROR_=1 _N_=1
NOTE: The data set WORK.TEST has 1 observations and 1 variables.

If you want to enter a date constant then you need use a quoted string followed by the letter D. The string inside the quotes needs to be something that the DATE informat can convert into a date value.

if "01-JAN-2020"d <= doi <= '29 MAR 2020'd then put 'YES';
if "01JAN2020"D <= doi <= '29MAR20'd then put 'YES';

You could also use the MDY() function to generate a date value.

if mdy(1,1,2020) <= doi <= mdy(3,29,2020) then put 'YES';

Or any other function that can generate a date value. Like the INPUT() function.

if input("2020-01-01",yymmdd10.) <= doi <= '29 MAR 2020'd then put 'YES';

It makes no difference to the comparison operators in the IF statement what date type format (YYMMDD, DATE, DDMMYY, MMDDYY, etc.) you have attached to the variable. Formats are just instructions for how to convert values to text, they do not change the values that are stored.

The key thing is that you need to compare the date values in your variables to other date values in your IF statement. The examples above show a number of different ways of getting a date value into your program.

Upvotes: 1

PeterClemmensen
PeterClemmensen

Reputation: 4937

  • "2020-01-01" is a string.
  • "01Jan2020"d is a date constant.

See if that works for you :-)

Upvotes: 1

Related Questions