topic Re: macro variable as a column value in SAS Programming

macro variable as a column value

jepafob — Thu, 01 Oct 2020 07:46:54 GMT

data _null_;
call symput('today',put(intnx('month',today(),-1),MONYY.));
run;
%put &today.;

data test;
date=&today.;
run;

output:

645  %put &today.;
SEP20
646
647  data test;
648  date=&today.;
649  run;

NOTE: Variable SEP20 is uninitialized.
NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds


NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of this
      session.

want:

table with one column named 'date' and value that resolves to, the macro variable above.

like:

date

SEP20

have for now:

Re: macro variable as a column value

SASKiwi — Thu, 01 Oct 2020 08:00:38 GMT

Try this - to specify a proper SAS date variable you need to include the day, month and year:

data _null_;
call symput('today',put(intnx('month',today(),-1),MONYY.));
run;
%put &today.;

data test;
format date date9.;
date="01&today."d;
run;

Re: macro variable as a column value

gamotte — Thu, 01 Oct 2020 08:11:04 GMT

Hello,

What is the need of using a macro-variable ? Just set your date variable directly.

data have;
format date monyy.;
date=intnx('month',today(),-1);
run;

Re: macro variable as a column value

Kurt_Bremser — Thu, 01 Oct 2020 08:11:10 GMT

Maxim 33 and Maxim 28.

Keep date values as SAS dates and use formats.

Do not format macro variables, unless you need them in human-readable form (e.g. in titles or labels).

%let today = %sysfunc(intnx(month,%sysfunc(today()),-1));

data test;
date = &today.;
format date monyy.;
run;

proc print data=test;
run;

Result:

Beob.	date
1	SEP20

Re: macro variable as a column value

PaigeMiller — Thu, 01 Oct 2020 11:18:39 GMT

date=&today.;

You get the incorrect results because macro variables are just text, they do not have any other meaning, and they do not have the meaning that they are actual dates.

So the line of code above uses text substitution, the value of the macro variable is substituted in the place of the macro variable when SAS runs, and the line above becomes

date=SEP20;

which then is evaluated as DATA step code, and SAS interprets SEP20 not as a date, but as variable named SEP20 (because that's what it means in a DATA step), and tries to assign the value of variable SEP20 to DATE, but variable SEP20 doesn't exist. So SAS does what you told it to do, assign to the variable DATE the value of variable SEP20, which doesn't exist.

This is why other people have advised you to use just plain old DATA step code and avoid the complications of macros here. Others in this thread have also explained how to get your macro variables to work properly. For example, @SASKiwi has specifically included in his code instructions for the data step to interpret the macro variable as a SAS date value.