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.
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
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.
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 >=
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
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.
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 ¯ovar instead of "¯ovar"d (in case of dates).
So stay with the way you used &MD.
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: