DATA Step, Macro, Functions and more

Need help with dates

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 124
Accepted Solution

Need help with dates

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


Accepted Solutions
Solution
‎07-28-2013 12:06 AM
Super User
Super User
Posts: 6,502

Re: Need help with dates

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


All Replies
Super Contributor
Posts: 1,636

Re: Need help with dates

try:

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

or

If Date_variable >= '26jul2013'd;

Super User
Posts: 10,521

Re: Need help with dates

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.

Super Contributor
Posts: 1,636

Re: Need help with dates

if Date_variable is a character variable, then try:

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

Trusted Advisor
Posts: 1,131

Re: Need help with dates

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
Valued Guide
Posts: 797

Re: Need help with dates

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

Trusted Advisor
Posts: 1,131

Re: Need help with dates

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
Solution
‎07-28-2013 12:06 AM
Super User
Super User
Posts: 6,502

Re: Need help with dates

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...

Trusted Advisor
Posts: 1,131

Re: Need help with dates

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
Frequent Contributor
Posts: 124

Re: Need help with dates

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

Contributor
Posts: 37

Re: Need help with dates

[ Edited ]

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??

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 7104 views
  • 8 likes
  • 7 in conversation