topic Re: Date Macro Variable Format to Use in a Datastep in SAS Programming

Date Macro Variable Format to Use in a Datastep

buffalol — Wed, 14 Jun 2017 10:04:17 GMT

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.

Re: Date Macro Variable Format to Use in a Datastep

RW9 — Wed, 14 Jun 2017 10:41:44 GMT

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.

Re: Date Macro Variable Format to Use in a Datastep

buffalol — Wed, 14 Jun 2017 11:15:04 GMT

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

Re: Date Macro Variable Format to Use in a Datastep

Astounding — Wed, 14 Jun 2017 11:25:38 GMT

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

Re: Date Macro Variable Format to Use in a Datastep

buffalol — Wed, 14 Jun 2017 11:30:32 GMT

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.

Re: Date Macro Variable Format to Use in a Datastep

Kurt_Bremser — Wed, 14 Jun 2017 11:38:52 GMT

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.