BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tegan
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
Tom
Super User Tom
Super User

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)


Tegan
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

ballardw
Super User

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

Tegan
Calcite | Level 5

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

art297
Opal | Level 21

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.

Tegan
Calcite | Level 5

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

art297
Opal | Level 21

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

Tegan
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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