I am doing some formatting on a report and I have a %let statement that formats a border based on certain criteria.
The first line works. But if I add the 2nd line for CAT2, it doesnt like it. how can i code this to allow for multiple styles?
%let borderfmt = if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT2') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
Why are you putting CODE into a macro variable?
If you want to put ; into a macro variable using a %LET statement then you will need to add some quoting.
You could add actual quotes, but then they become part of the value and if you want to use the value to actually generate those statements you will need to remove them.
You could add macro quoting.
You might try the %NRSTR() macro function.
%let borderfmt = %nrstr(
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT2') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
);
%let borderfmt = if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
That semi-colon at the end of the line above ends the %LET statement. If then you add in this:
else if CATEGORY IN ('CAT2') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
It is not part of %let borderfmt, it is just an ELSE statement in open code, and SAS doesn't know what to do with ELSE statements like that, with no related IF. Thus the suggestion to use %NRSTR(), which fixes the matter.
I tried this:
%let borderfmt = %nrstr(
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
);
but it seems to completely ignore the formatting
I suspect the IF isn't valid where you're using it. Can you show how this statement is then used? Ideally a reproducible example (reprex) is helpful in these situations and for debugging.
@Jyuen204 wrote:
I tried this:
%let borderfmt = %nrstr(
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
);
but it seems to completely ignore the formatting
@Jyuen204 wrote:
I tried this:
%let borderfmt = %nrstr(
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
);
but it seems to completely ignore the formatting
Saying something doesn't work, and then providing no additional information, is never helpful. We don't know what you are seeing.
If there are errors or warnings in the log for this PROC, then show us the ENTIRE log for this PROC. Do not show us partial logs for this PROC, we need to see every single line of the log for this PROC.
If there are no errors or warnings in the log, but the output is not right, show us the entire code for this PROC, and the output and explain why it is wrong.
This is the code
%let borderfmt = %nrstr(
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]');
else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]');
);
/*CREATE EXCEL PACKAGE TO GO OUT*/
/*%let report_dir = %sysfunc(pathname(work));*/
DATA _NULL_;
ENAME_DT = PUT(DATE(),DATE9.);
ENAME = STRIP(("REPORT_" ||ENAME_DT||".xls"));
CALL SYMPUT("ENAME", ENAME);
RUN;
%LET OUTPUT_PATH = /data/bi/data/inbox/;
ODS LISTING CLOSE;
ods tagsets.ExcelXP style=sasweb path= "&OUTPUT_PATH" file= "&ENAME" Style=Printer;
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='10,20,5,5,5,5,5,5,5,5,7,7,7,7,7');
%report_page1(BO_WEEKLY_RESULTS);
ods tagsets.ExcelXP close;
We cannot see your data so we don't know if CATEGORY ever has the values CAT1 or CAT1.
Remember that 'CAT' and ' CAT' and 'Cat' and 'cat' are all different values. But the first two will look exactly the same in output generated via ODS.
@Jyuen204 wrote:
those were generated examples of the values in the Category field.
I input the specific values into that field so I wanted to apply a border based on the specific valie I set in the field.
Double check that PROC REPORT is reading from the dataset that has the right values of CATEGORY.
You should turn on MPRINT so you can see what code the macro generated.
Another thing to consider is why are you using TAGSETS.EXCELXP to generate an XML file instead of using EXCEL to generate an actual XLSX file?
It's helpful to see your macro code, but it's still hard to help without a small reproducible example. Typically for something like this you can create a small dataset (3-5 records) using the CARDS statement, and focus on only the problematic code. Often when you take the time to make a reproducible example that you can share, you will find the answer yourself.
Another rule when working on something you think is a macro language problem, is to get the code working first without the macro language. This is helpful even when you've inherited a macro and you need to update it.
I took your macro, made a tiny example, got it to run, and then realized the problem isn't in the macro language, I think the problem is that in your PROC REPORT step, you set borderwidth to 0, so when your CALL DEFINE turns on borders, they still have a width of zero.
Below is my play code. I suggest you play with something like this, to get it working like you want, with your real data. Then after you have hard-coded SAS code that works like you want, return to the macro language to work on the macro design.
data BO_WEEKLY_RESULTS ;
input Category : $4. Wow : $1. ;
cards ;
CAT1 A
Dog B
CAT C
;
ods tagsets.ExcelXP style=sasweb path= "%sysfunc(pathname(work))" file= "try.xls" Style=Printer; *why are there two style= specifications? ;
proc report data=BO_WEEKLY_RESULTS split='#' spanrows
style(report)=[outputwidth = 100% cellspacing=0 cellpadding=2 frame = hsides rules=groups font=(Arial, 8pt, bold)]
style(header)=[foreground=white background=CX0066a4/*00669A*/ font=(Arial, 9pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white NOBREAKSPACE=ON ]
style(column)=[font=(Arial, 8pt) foreground=black background=CXFFFFFF VJUST=C borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ]
;
Columns
CATEGORY
WOW
;
Define WOW / CENTER "WOW PCT";
Compute WOW;
if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=red borderbottomwidth=2]');
else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=red bordertopwidth=2]');
endcomp;
run;
ods tagsets.ExcelXP close;
That code 'works' for me (in the sense that it conditionally adds a red border). But it's probably not what you want, as the cells lose the other borders. So this will still need some tinkering. Perhaps you can set borderwidth on the style(column) option instead.
What formatting?
Are you talking about color coding in the EDITOR window? You can ignore that as it is just the editor's GUESS as to what the code is. The editor is NOT SAS.
Or are you talking about the formatting in the generated REPORT that you perhaps used the value of that macro variable to generate?
Are you sure the variable CATEGORY ever has either of those two values?
Are you sure some other code afterwards isn't changing the border color?
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: