COLUMN NAME(Variable) IS A MACRO USING THAT IN PROC REPORT

[Kirito1]

I was working on SAS and One of the variable or column names is a macro. But When I am using this in PROC REPORT its giving me an error. Can I know How to use macros as column name while using proc report. The macros are as below.

%let name2= "DATE_%sysfunc(putn(%sysfunc(today())-2,date9.))"n;

%let name1="DATE_ %sysfunc(putn(%sysfunc(today())-1,date9.))"n;

%let name="DATE_ %sysfunc(putn(%sysfunc(today()),date9.))"n;

PROC REPORT DATA=FINAL_DST_REPOT_TABLE nowd spanrows
style(report)=[JUST=CENTER OUTPUTWIDTH=95% CELLSPACING=2 BORDERCOLOR=BLACK BORDERWIDTH=2]
STYLE(HEADER)={BORDERCOLOR=BLACK FOREGROUND=WHITE BACKGROUND=MAROON FONT=("zurich BT",11pt)}
STYLE(COLUMN)={TAGATTR="WRAP" JUST=CENTER FONT=("zurich BT",08pt) OUTPUTWIDTH=0.05IN BACKGROUND=LIGHTYELLOW
FOREGROUND=BLACK BORDERCOLOR=BLACK};

TITLE FONT="Zurich BT" bold height=6 "<U>DST REPORT</U>" JUSTIFY=CENTER;
TITLE2 FONT="Zurich BT" bold height=2 "<U>Report Run Date: &sysdate. Time: &SYSTIME</U>" JUSTIFY=RIGHT;

column AM AM_CODE TL  TL_CODE Emp_Code Emp_Name &name2 &name1 &name DRR Target APPROVED 
ASSIGNED RESEND UNASSIGNED TOTAL_MTD_DEPLOYED DEC22_LMTD_APPROVED GAP 
APP_LOGIN ACTIVE_JAN2023 INACTIVE_JAN2023 ACTIVE_DEC2022 QR_APPROVED_DEC2022 APP_LOGIN_DEC2022;
define AM/"AM" order order=data style(column)=[font=("zurich BT",9pt)];
define AM_CODE/"AM Code" order order=data style(column)=[font=("zurich BT",9pt)];
define TL/"TL Name" order order=data style(column)=[font=("zurich BT",9pt)];
define TL_Code/"TL Code"order order=data style(column)=[font=("zurich BT",9pt)];
define Emp_Code/"Emp Code" style(column)=[font=("zurich BT",9pt)];
define Emp_name/"Employee Name" style(column)=[font=("zurich BT",9pt)];
define &name2/"&name2"  style(column)=[font=("zurich BT",9pt)];
define &name1/"&name1"  style(column)=[font=("zurich BT",9pt)];
define &name/"&name"  style(column)=[font=("zurich BT",9pt)];
define DRR/"DRR" style(column)=[font=("zurich BT",9pt)];
define Target/"TARGET" style(column)=[font=("zurich BT",9pt)];
define APPROVED/" " style(column)=[font=("zurich BT",9pt)];
define ASSIGNED/"ASSIGNED" style(column)=[font=("zurich BT",9pt)];
define RESEND/"Resend" style(column)=[font=("zurich BT",9pt)];
define UNASSIGNED/"UNASSIGNED" style(column)=[font=("zurich BT",9pt)];
define TOTAL_MTD_DEPLOYED/"TOTAL_MTD_DEPLOYED" style(column)=[font=("zurich BT",9pt)];
define DEC22_LMTD_APPROVED/"DEC22_LMTD_APPROVED" style(column)=[font=("zurich BT",9pt)];
define GAP/"GAP" style(column)=[font=("zurich BT",9pt)];
define APP_LOGIN/"APP_LOGIN" style(column)=[font=("zurich BT",9pt)];
define ACTIVE_JAN2023/"ACTIVE_JAN2023" style(column)=[font=("zurich BT",9pt)];
define INACTIVE_JAN2023/"INACTIVE_JAN2023" style(column)=[font=("zurich BT",9pt)];
define ACTIVE_DEC2022/"ACTIVE_DEC2022" style(column)=[font=("zurich BT",9pt)];
define QR_APPROVED_DEC2022/"QR_APPROVED_DEC2022" style(column)=[font=("zurich BT",9pt)];
define APP_LOGIN_DEC2022/"APP_LOGIN_DEC2022" style(column)=[font=("zurich BT",9pt)];
RUN;

ERROR:

Please, help.

Thanks

[Kirito1]

Please, someone help.

[PaigeMiller]

Point #1: Please post the log by copying at text (not screen capture) and pasting it into the window that appears when you click on the </> icon. From now on, please provide the log this way and not as a screen capture.

Point #2: we need to see the ENTIRE log for this PROC REPORT, not just the error messages. From no on, we need to see the entire log for a PROC or DATA step.

Point #3: you were strongly advised by several experienced SAS programmers to not create variable names that were calendar dates, but you have continued to do so, causing problems now, and which will likely cause problems in the future. You really ought to go back and restructure your data so that the date is in a variable (perhaps the variable is named DATE) and not in a variable name.

Terminology: you do not have a macro. You have a macro variable. These are different. Do not use the word "macro" to refer to a macro variable.

[Kirito1]

I will keep a check for Point1 and Point2. But regarding Point 3, but what if this is the requirement of the client is there a way round it. They made it clear that dates should be displayed in report

Thanks for the suggestions much appreciated.

[Tom · Accepted solution]

From the error message you appear to have added the quotes twice on the lines like this:

define &name1/"&name1"  style(column)=[font=("zurich BT",9pt)];

You set the macro variables to contain quotes.

%let name1="DATE_ %sysfunc(putn(%sysfunc(today())-1,date9.))"n;

Then added quotes around them when you used them, so that the result would be:

define "DATE_ 19JAN2023"n/""DATE_ 19JAN2023"n" style(column)=[font=("zurich BT",9pt)];

which is not valid SAS syntax.

Just generate the names without the quotes. The values you are storing do not need them.

%let name1=DATE_%sysfunc(putn(%sysfunc(today())-1,date9.));

Then your other code will generate valid SAS syntax:

define DATE_19JAN2023/"DATE_19JAN2023" style(column)=[font=("zurich BT",9pt)];

Or since you seem to want to use the name of the label why not just remove that part of the code?

define &name1/  style(column)=[font=("zurich BT",9pt)];

If for some reason there is some other unwanted label already attached to the variable named by &NAME1 then add a LABEL statement to remove it.

label &name1=' ';

[ballardw]

Lets look at some pieces of the code:

%let name1="DATE_ %sysfunc(putn(%sysfunc(today())-1,date9.))"n;

So you create value for the macro variable Name1 that looks like "Date_19Jan2023"n

Then in this line of code

define &name1/"&name1"  style(column)=[font=("zurich BT",9pt)];

So you have a result that looks like

define "Date_19Jan2023"n / ""Date_19Jan2023"N" <style options>;

See a problem with quotes? "" is a closed string so Date_19Jan2023 is taken as some sort of Define option and really is not. Then you have a quoted "N" after that. And you did this three time.

I think that we may have also recommended not placing quotes is part of the macro variable. This is one of the reasons, if you want to use the value inside a quoted string, such as Title, Label, Footnote or similar text, then you have to go through a lot of extra work to avoid this reference error.

%let name1=DATE_ %sysfunc(putn(%sysfunc(today())-1,date9.));

/* the corresponding define:*/

Define "&name."n / "&name." <style options>;

[PaigeMiller]

You don't need macro variables here

define &name2/"&name2"  style(column)=[font=("zurich BT",9pt)];
define &name1/"&name1"  style(column)=[font=("zurich BT",9pt)];
define &name/"&name"  style(column)=[font=("zurich BT",9pt)];

You can replace this with an ACROSS variable, such as a variable named DATE, and all of a sudden no macro variables are needed and the trouble you are having with macro variables goes away.

Small example:

data have;
    input state $ date :mmddyy8. sales;
    datalines;
NY 12102022 273
NY 12112022 212
NY 12122022 309
IL 12102022 39
IL 12112022 49
IL 12122022 78
IL 12132022 109
;
 
proc report data=have split='~';
    columns state date,sales;
    define state/group 'State';
    define date/across 'DATE' format=mmddyy10.;
    define sales/sum 'Sales';
run;

In this example, I have a column named DATE, and yet in PROC REPORT, because I specify DATE to be an ACROSS variable, I get a column in my output named 12/10/2022, even though there is no such variable in my data set named '12/10/2022'n. In addition, SAS figures out what dates are in your data set, and so you don't have to create those names like '12/01/2022'n.

And thus the programming works unchanged in tomorrow you want to run the program with different dates in the data set, and there is no struggling with macro variables to figure out what dates are in the data and then create variable names with those dates.

And so all the other code provided by others to help you get dates into variable names is simply not necessary. @Kirito1 make your programming much simpler and use ACROSS variables in PROC REPORT and not macro variables whose value is the date like '12/10/2022'n.

[Kirito1]

Thanks for the alternate solution as well. 🙂 Much appreciated.