BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gsnidow
Obsidian | Level 7

Greetings.  In a dataset I have a date variable that is (or should be) a date.  It is in format 'YYYY-MM-DD', and when I look at the dataset properties for that column, the format is YYMMDD10. and the Informat is YYMMDD10.  So, in my code, I want to put some conditions on the date...

If Date_variable >= '2013-07-26'd  Then...

But I keep getting error: ERROR: Invalid date/time/datetime constant '2013-07-26'd.  I have tried '2013-07-26'd, '20130726'd, '26JUL2013'd, '07/26/2013'd, and everything else I could think of, and I am still getting errors.  I'm sure it something simple (and very frustrating), but I can't make it work.  Can anyone help?  Thank you.

Greg

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Date literals in SAS only work with DATE. format.  So you can use '26JUL2013'd but you cannot use '2013-07-26'd.

So it is probably easiest to just change your code to specify date literals in the form that SAS wants.

Now if the source of the data for the dates is not just the program code and it is only available in yymmdd format you can convert it using the INPUT() or INPUTN() function.  If you have it as a macro variable then you can use %sysfunc() to call the INPUTN() function to convert to either the internal date value (days since 1/1/1960) or DATE9 format that you CAN use as a date literal..

%let keydate=2013-07-26;

%let keydate=%sysfunc(inputn(&keydate,yymmdd10.),date9.);

....

If Date_variable >= "&keydate"d  Then...

View solution in original post

10 REPLIES 10
Linlin
Lapis Lazuli | Level 10

try:

If Date_variable >= mdy(07,26,2013);

or

If Date_variable >= '26jul2013'd;

ballardw
Super User

Use of '26JUL2013'd for your example should have worked. Please try with this value and then if the error persists copy the code and entire error message from the log and post here.

Linlin
Lapis Lazuli | Level 10

if Date_variable is a character variable, then try:

input(date_variable,yymmdd10.)>mdy(7,26,2013);

Jagadishkatam
Amethyst | Level 16

Since both the format and the informat are yymmdd10. for the date, make sure that the date_variables are also in the same format and informat,

then you can use the following code

if date_variable>=input('2013-07-26',yymmdd10.) ;

Hope this helps.

Thanks,

Jagadish

Thanks,
Jag
mkeintz
PROC Star

Why would you ever have

a)  "if date_variable>=input('2013-07-26',yymmdd10.);"

instead of

b) "if date_variable>='26jul2013'd;"

Both versions assume that date_variable is (1) numeric, and (2) contains a date value.  It matters not what format or informat has been assigned to the variable.  And version (a) will ask SAS to needlessly run the INPUT function NOBS time, always getting the same result, namely '26jul2013'd for comparison purposes.  Version b has no such overhead.

Mark

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jagadishkatam
Amethyst | Level 16

The main reason for suggesting the use of the code "if date_variable>=input('2013-07-26',yymmdd10.) ;", as per the query the date when used this way '2013-07-26'd is not recognized by the system and is producing the errors as below

ERROR: Invalid date/time/datetime constant '2013-07-26'd.

ERROR 77-185: Invalid number conversion on '2013-07-26'd.

The system is unable to recognize the date given in the format '2013-07-26'd, so it is essential that we read the date using the input function and then pass the condition.

This code "if date_variable>='26jul2013'd;" will work well. However i have given an alternative way to this.


Both the way will definitely work.

i am open to any further suggestions.

Thanks,

Jagadish

Thanks,
Jag
Tom
Super User Tom
Super User

Date literals in SAS only work with DATE. format.  So you can use '26JUL2013'd but you cannot use '2013-07-26'd.

So it is probably easiest to just change your code to specify date literals in the form that SAS wants.

Now if the source of the data for the dates is not just the program code and it is only available in yymmdd format you can convert it using the INPUT() or INPUTN() function.  If you have it as a macro variable then you can use %sysfunc() to call the INPUTN() function to convert to either the internal date value (days since 1/1/1960) or DATE9 format that you CAN use as a date literal..

%let keydate=2013-07-26;

%let keydate=%sysfunc(inputn(&keydate,yymmdd10.),date9.);

....

If Date_variable >= "&keydate"d  Then...

Jagadishkatam
Amethyst | Level 16

Yes Tom, the sas is unable to recognize date in 'yymmdd'd literals, so the best way would be to read the date with the informat yymmdd10. and use the date9. format as rightly mentioned by you.

Thanks,
Jag
gsnidow
Obsidian | Level 7

Thank you all so much for the help.  Dates in SAS are proving troublesome to get.  The data is coming from an ODBC connection to DB2, where all dates are in yyyy-mm-dd format (according to Wiki, it is ISO 8601 format).  Anyhow, I think the best thing to do is to format them so I can use literals.  For some reason our IE at work will not allow most of the buttons to work on the forum, so I was able to add a topic, but then I could not navigate back to it when I came back to work the next week.  Thanks again everyone.

Greg

Lenvdb
Quartz | Level 8

Yep - Having the same problems. My manager insists we use ISO8601 format, but SAS has no inclination to honour ISO standards. 

 

We put postfix on datasets as _yyyymmdd and declare such dates as Macro variables.

 

Only when you try to read such a date into a dataset with a Where clause we get terrible problems:

 

 

%let dtToday = %sysfunc(date(),yymmddn8.);

Testfile is specified as 
Data TestFile;
Length FileName $30.;
/*===We use a Tab as a delimiter===*/
Infile Datalines dlm='09'x;
Input LoadDate Date9. FileName $ VerNo 2.;
FORMAT LoadDate Date9. FileName $30.;
Datalines;
30NOV2016 20161130_1.zip 1
01DEC2016 20161201_2.zip 2
02DEC2016 20161202_3.zip 3
05DEC2016 20161205_4.zip 4
06DEC2016 20161206_5.zip 5
;
Run;

Data test;
SET TestFile;
where LoadDate = inputn(&dtToday,yymmddn8.);
/*where LoadDate = inputn(&dtToday,Date9.);
where LoadDate = inputn(&dtToday,yymmdd10.);
where LoadDate = "&dtToday"d;*/
/*where LoadDate = &dtToday;*/
run; 31 where LoadDate = inputn(&dtToday,yymmdd10.); _________ 201 ERROR 201-322: The option is not recognized and will be ignored. ERROR: Syntax error while parsing WHERE clause.

This should not be so hard - why is this proving to be so extremely difficult??

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 17065 views
  • 11 likes
  • 7 in conversation