Help using Base SAS procedures

Proc Export w/Where ddmmyyyy

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Proc Export w/Where ddmmyyyy

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?


additional information.JPGadditional information 2.jpg

Accepted Solutions
Solution
‎04-14-2014 03:52 PM
Super User
Super User
Posts: 7,062

Re: Proc Export w/Where ddmmyyyy

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)


View solution in original post


All Replies
Solution
‎04-14-2014 03:52 PM
Super User
Super User
Posts: 7,062

Re: Proc Export w/Where ddmmyyyy

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)


Occasional Contributor
Posts: 14

Re: Proc Export w/Where ddmmyyyy

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

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.

Super User
Super User
Posts: 7,062

Re: Proc Export w/Where ddmmyyyy

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

Super User
Posts: 11,343

Re: Proc Export w/Where ddmmyyyy

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

Occasional Contributor
Posts: 14

Re: Proc Export w/Where ddmmyyyy

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

PROC Star
Posts: 7,484

Re: Proc Export w/Where ddmmyyyy

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.

Occasional Contributor
Posts: 14

Re: Proc Export w/Where ddmmyyyy

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

PROC Star
Posts: 7,484

Re: Proc Export w/Where ddmmyyyy

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

Occasional Contributor
Posts: 14

Re: Proc Export w/Where ddmmyyyy

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 3153 views
  • 0 likes
  • 4 in conversation