Date Macro Variable Format to Use in a Datastep

[buffalol]

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. 

[RW9]

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]

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 · Accepted solution]

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]

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]

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.