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?
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)
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)
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)
-----
22
76
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.
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
And date literal can only be used used as '10MAR1992'd, if you try '01/10/1992'd that will also fail.
Does that mean I have to change all the dates in that column to a '10MAR1992' format? How do I do that?
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.
These are the column attributes. The file is an import from Excel, and I have not done anything to the formatting.
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))
After getting over some of my lazy Monday coding, Tom's suggestion worked! Thank, Tom!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.