topic Re: Need help with dates in SAS Programming

Need help with dates

gsnidow — Fri, 26 Jul 2013 19:23:55 GMT

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

Re: Need help with dates

Linlin — Fri, 26 Jul 2013 19:29:01 GMT

try:

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

If Date_variable >= '26jul2013'd;

Re: Need help with dates

ballardw — Fri, 26 Jul 2013 19:30:16 GMT

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.

Re: Need help with dates

Linlin — Fri, 26 Jul 2013 19:38:07 GMT

if Date_variable is a character variable, then try:

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

Re: Need help with dates

Jagadishkatam — Sat, 27 Jul 2013 05:16:23 GMT

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

Re: Need help with dates

mkeintz — Sun, 28 Jul 2013 02:11:17 GMT

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

Re: Need help with dates

Jagadishkatam — Sun, 28 Jul 2013 03:40:40 GMT

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

Re: Need help with dates

Tom — Sun, 28 Jul 2013 04:06:42 GMT

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

Re: Need help with dates

Jagadishkatam — Sun, 28 Jul 2013 04:23:33 GMT

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.

Re: Need help with dates

gsnidow — Wed, 07 Aug 2013 00:34:09 GMT

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

Re: Need help with dates

Lenvdb — Fri, 02 Dec 2016 12:38:28 GMT

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