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

I have seen similar questions in the forums and tried various things, but am not getting any result. 

 

What I'm trying to achieve: 

I need to get the maximum date from a dataset and then use this date to retrieve a subset from an existing dataset. I've used Proc SQL to get the maximum date and stored this as a macro variable. The trouble is that it is no longer in DATE. format. So when I try to use my variable in the datastep I get an error about non-compatible variables. 

 

My current code where the error occurs:

 

SET testdata;

WHERE (&MD - 364) <= DATE <= '20MAY06';

 

Other notes:

&MD is the maximum date, when I format as FORMAT &MD DATE.; it works fine. However it does not stay that way for my WHERE clause. The date cannot be hard-coded. 

 

Many thanks. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Most likely, the error has nothing to do with &MD.  As far as SAS is concerned, &MD can remain unformatted, and still be part of a valid date comparison.

 

The problem lies on the other side of the comparison.  '20MAY06' is a character string, not a date.  I doubt that the date you are comparing it to is a character string (but if it is that opens up a larger set of issues).  Instead of using '20MAY06' change that to '20MAY2006'd

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You do not need macro for this, one SQL step can do it all:

proc sql;
  create table WANT as
  select  *
  from    (select max(DATE) from OTHERDATASET) <= DATE <= "20MAY2006"d;
quit;

Note that you have not provided any test data (in the form of a dataset) for us to work with, so this is all guesswork.  Also your where clause second part will not work, "20MAY06" is not a valid date valid, not even if you put a d after it for date literal.

Finally, dates are numeric, it should not matter if the macro resolves to 10856 or something, as that should still be comparable to the DATE variable if its numeric.  If you post your full question, providing sample test data in the form of a datastep and what the output should look like then we can tailor the code to your needs. 

buffalol
Fluorite | Level 6

I am currently doing training and one of the questions asks for the maximum date to be called MD. I assume for use in later exercises. Essentially I am asking how to convert a macro variable to DATE.(which displays as 10FEB01 in columns) format and how to use it in one or more datasteps later. I need to minus 364 days from the Maxdate and then subset an existing dataset using a known date and the maxdate retrieved earlier. 

 

So (Maxdate - 364) up to knowndate. 

 

I tried using your code but it did not work. Got a syntax error on the the comparison operator >=

 

 

 

Astounding
PROC Star

Most likely, the error has nothing to do with &MD.  As far as SAS is concerned, &MD can remain unformatted, and still be part of a valid date comparison.

 

The problem lies on the other side of the comparison.  '20MAY06' is a character string, not a date.  I doubt that the date you are comparing it to is a character string (but if it is that opens up a larger set of issues).  Instead of using '20MAY06' change that to '20MAY2006'd

buffalol
Fluorite | Level 6

That was exactly it. All this time and that was the only thing I had to change. I'm not sure whether to feel happy or sad at this point. Thank you. 

Kurt_Bremser
Super User

See my Maxim 28. For use in comparisons or value assignments, macro variables do not need to have formatted values; it is usually better to keep the raw values, as you can simply use &macrovar instead of "&macrovar"d (in case of dates).

So stay with the way you used &MD.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1827 views
  • 2 likes
  • 4 in conversation