topic Re: Proc Export w/Where ddmmyyyy in SAS Procedures

Proc Export w/Where ddmmyyyy

Tegan — Mon, 14 Apr 2014 19:22:27 GMT

I'm trying to proc export with a where clause on a date column and it's not working.

I think it has something to do with the format of the date variables: ddmmyyyy

Proc export data=MAUDE.Total1999thru2013 (where=(DATE_RECEIVED='03/10/1992'))

dbms=excel file="...\Output\test9.xls";

run;

Error message is:

OTE: File "...\Output\test9.xls" will be created if the export process succeeds.

ERROR: WHERE clause operator requires compatible variables.

ERROR: Export unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.85 seconds

cpu time 0.17 seconds

Any tips or wise words?

Re: Proc Export w/Where ddmmyyyy

Tom — Mon, 14 Apr 2014 19:52:48 GMT

If it is really a date variable they you should use a date value and not a character string in your WHERE clause.

where=(DATE_RECEIVED='10MAR1992'D)

Re: Proc Export w/Where ddmmyyyy

Tegan — Mon, 14 Apr 2014 21:30:04 GMT

My data is in format dd/mm/yyyy, and I'm still getting an error, albeit a different one...

456 Proc export data=MAUDE.Total1999thru2013 where=(DATE_RECEIVED='10MAR1992'D)

457 dbms=excel file="="...\Output\test9.xls";

458 run;

459 Proc export data=MAUDE.Total1999thru2013 where=(DATE_RECEIVED='10MAR1992'D)

-----

ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, DBLABEL, DBMS, DEBUG,

FILE, LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Proc Export w/Where ddmmyyyy

ballardw — Mon, 14 Apr 2014 21:30:14 GMT

And date literal can only be used used as '10MAR1992'd, if you try '01/10/1992'd that will also fail.

Re: Proc Export w/Where ddmmyyyy

Tegan — Mon, 14 Apr 2014 21:32:09 GMT

Does that mean I have to change all the dates in that column to a '10MAR1992' format? How do I do that?

Re: Proc Export w/Where ddmmyyyy

art297 — Mon, 14 Apr 2014 21:36:54 GMT

Are the actual values in the date variable numbers formatted as SAS dates? If they are, Tom's suggested code should do what you want without you having to change your data.

Re: Proc Export w/Where ddmmyyyy

Tom — Mon, 14 Apr 2014 21:38:12 GMT

The error message is because you left off the closing right parenthesis for your data set options.

It does not matter what format you have chosen to display the dates the WHERE clause is going to act on the actual VALUE that is stored.

In the case of a date that is the number of days since 1/1/1960. You also just use date_received = 11757 if you wanted.

5 data _null_;

6 x='10MAR1992'd ;

7 put x= / x= date9. / x=mmddyy10. / x= yymmdd10. ;

8 run;

x=11757

x=10MAR1992

x=03/10/1992

x=1992-03-10

Re: Proc Export w/Where ddmmyyyy

Tegan — Mon, 14 Apr 2014 21:39:49 GMT

These are the column attributes. The file is an import from Excel, and I have not done anything to the formatting.

Re: Proc Export w/Where ddmmyyyy

art297 — Mon, 14 Apr 2014 21:44:31 GMT

Look at Tom's last response: you left off two parentheses. Instead of:

Proc export data=MAUDE.Total1999thru2013 where=(DATE_RECEIVED='10MAR1992'D)

use

Proc export data=MAUDE.Total1999thru2013 (where=(DATE_RECEIVED='10MAR1992'D))

Re: Proc Export w/Where ddmmyyyy

Tegan — Mon, 14 Apr 2014 21:49:03 GMT

After getting over some of my lazy Monday coding, Tom's suggestion worked! Thank, Tom!