%let If Then Else

[Jyuen204]

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 ]');

[Tom]

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 ]');
);

[Jyuen204]

I'm modifying some existing report and was following in what was coded before so as to not break anything pre existing. There are a number of %let statements in the formatting section of the code that gets pulled into the main report.

[PaigeMiller]

%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.

[Jyuen204]

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

[Reeza]

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

---

[PaigeMiller]

---

@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.

[Jyuen204]

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 ]');
);

%macro report_page1( inTable );

proc report data=&inTable 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 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ];

Columns

CATEGORY

METRIC

("Previous Weeks"

P6W

P5W

P4W

P3W

P2W

PW

CW

)

(

    "WTD"

WTD

)

("WOW"

WOW

)

("MONTHLY"

P3M

P2M

PM

)

("MTD"

MTD

)

("YTD"

YTD

)

;

    Define CATEGORY / order=data "CATEGORY";

Define METRIC / order=data "METRIC";

Define P6W / CENTER "&P6W" format = comma12.;

    Define P5W / CENTER "&P5W" format = comma12.;

Define P4W / CENTER "&P4W";

Define P3W / CENTER "&P3W";

Define P2W / CENTER "&P2W";

Define PW / CENTER "&PW";

Define CW / CENTER "&CW";

Define WOW / CENTER "WOW PCT";

Define P3M / CENTER "&P3M";

Define P2M / CENTER "&P2M";

Define PM / CENTER "&PM";

Define MTD / CENTER "&CM";

Define YTD / CENTER "YTD";

Compute METRIC; &color; &fontfmt; &borderfmt; endcomp;

Compute CATEGORY; &colorcat; &catfontfmt; &borderfmt; endcomp;

Compute WOW; &borderfmt; endcomp;

Compute CW;  &borderfmt; endcomp;

Compute WTD;  &borderfmt; endcomp;

Compute PW;  &borderfmt; endcomp;

Compute P2W;  &borderfmt; endcomp;

Compute P3W;  &borderfmt; endcomp;

Compute P4W; &borderfmt; endcomp;

Compute P5W;  &borderfmt; endcomp;

Compute P6W;  &borderfmt; endcomp;

Compute PM;  &borderfmt; endcomp;

Compute P2M;  &borderfmt; endcomp;

Compute P3M;  &borderfmt; endcomp;

Compute MTD;  &borderfmt; endcomp;

Compute YTD;  &borderfmt; endcomp;

run;

%mend report_page1;

/*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;

[PaigeMiller]

Hello, @Jyuen204 

I specifically said "show us the entire code for this PROC, and the output and explain why it is wrong."

You did not provide the part in red. Please provide that. Adding: and show us a portion of the data as requested by @Tom 

[Tom]

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]

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.

[Tom]

---

@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?

[Quentin]

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.

[Tom]

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?