topic Re: COMPUTE BLOCK in PROC REPORT in SAS Programming

COMPUTE BLOCK in PROC REPORT

cgsmoak56 — Mon, 22 Mar 2021 19:19:05 GMT

Hi,

I have the following compute block that works just fine.

compute dup_label;

if dup_label = 'Y' then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
end;

endcomp;

If there is a duplicate variable labels in the dataset, it shades the value of 'Y' in orange. I would also like in the report to shade the variable name and variable label in orange when the value of dup_label = 'Y'. I tried the following compute block, but it had no effect on the variable name and label.

compute VARNAME;

if dup_label = 'Y' then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
end;

endcomp;

compute VARLABEL;

if dup_label = 'Y' then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
end;

endcomp;

Is there a way to accomplish this task of shading in orange the variable name and label when dup_label = 'Y'?

Thank you in advance for your assistance.

Re: COMPUTE BLOCK in PROC REPORT

ballardw — Mon, 22 Mar 2021 19:27:29 GMT

When asking about Proc Report always include the entire procedure code. There are things that are dependent on the order of appearance on the Columns statement for instance.

Basic rule of thumb: proc report builds the report from right to left. So if you use a compute block for a variable and reference a variable that appears to the right of that column then that part of the code doesn't see the bit to the right.

So this is a guess:

compute dup_label;

if dup_label = 'Y' then do;
   call define(_COL_,"style","STYLE={background=#ffa500}");
   call define('varlabel',"style","STYLE={background=#ffa500}");
end;

endcomp;

You can name a specific variable column to modify as well as the present column or row with _col_ and _row_ locations.

Re: COMPUTE BLOCK in PROC REPORT

cgsmoak56 — Tue, 23 Mar 2021 15:47:37 GMT

Hi,

Here is the full code for this section of the program. Please look at the compute block for dup_label which I have bolded below. This code does turn the cells in the dup_label column orange when dup_label = 'Y'. However, it turn all cells for NAME and LABEL orange. I only want them to be orange for the instances where dup_label = 'Y'.

ods excel
options(sheet_name=%cmpres("&odsdata") sheet_interval='TABLE' gridlines='ON' flow='DATA');

proc report data=&odsdata nowd
style(report)=[bordercolor=black]
style(lines)=header{background=white asis=on font_size=10pt font_face="Arial" font_weight=bold color=#000000
just=left bordercolor=black}
style(header)=header{background=$color. font_size=10pt font_face="Arial" font_weight=bold color=#000000
frame=box bordercolor=black}
style(column)=header{background=white font_size=10pt font_face="Arial" font_weight=medium color=#000000
bordercolor=black};

columns ('_ODS_' NAME LABEL t_vtype t_LENGTH)
('_Source_' MEMNAME name_std label_new vtype LENGTH)
('_Instructions_' Mapping_Notes Variable_Use_Flag sdtm_supp dup_name dup_label Label_Length Comments)
;

define NAME / "Variable Name" flow
style(column)={cellwidth=1in}
style(header)={cellwidth=1in background=#8B9985};

define LABEL / "Variable Label" flow
style(column)={cellwidth=2.5in}
style(header)={cellwidth=2.5in background=#8B9985};

define t_vtype / "Type" flow
style(column)={cellwidth=0.6in}
style(header)={cellwidth=0.8in background=#8B9985};

define t_LENGTH / "Length" flow
style(column)={cellwidth=0.6in}
style(header)={cellwidth=0.6in background=#8B9985};

define MEMNAME / "Dataset" flow
style(column)={cellwidth=1in}
style(header)={cellwidth=1in background=#ffdab9};

define name_std / "Variable Name" flow
style(column)={cellwidth=1in}
style(header)={cellwidth=1in background=#ffdab9};

define label_new / "Variable Label" flow
style(column)={cellwidth=2.5in}
style(header)={cellwidth=2.5in background=#ffdab9};

define vtype / "Type" flow
style(column)={cellwidth=0.6in}
style(header)={cellwidth=0.8in background=#ffdab9};

define LENGTH / "Length" flow
style(column)={cellwidth=0.6in}
style(header)={cellwidth=0.6in background=#ffdab9};

define Mapping_Notes / "Mapping Notes" flow
style(column)={cellwidth=1in}
style(header)={cellwidth=1in background=#00FFFF};

define Variable_Use_Flag / "Variable Use Flag" flow
style(column)={cellwidth=1.25in}
style(header)={cellwidth=1.25in background=#00FFFF};

define sdtm_supp / "SDTM or Potential Suppqual" flow
style(column)={cellwidth=2in}
style(header)={cellwidth=2in background=#00FFFF};

define dup_name / "Duplicate Variable" flow
style(column)={cellwidth=1.25in}
style(header)={cellwidth=1.25in background=#00FFFF};

define dup_label / "Duplicate_Label" flow
style(column)={cellwidth=1.25in}
style(header)={cellwidth=1.25in background=#00FFFF};

define Label_Length / "Label Length" flow
style(column)={cellwidth=1.25in}
style(header)={cellwidth=1.25in background=#00FFFF};

define Comments / "Comments" flow
style(column)={cellwidth=1.25in}
style(header)={cellwidth=1.25in background=#00FFFF};

compute NAME;

call define(_COL_,"style","STYLE={background=#ffa500}");

endcomp;

compute LABEL;

call define(_COL_,"style","STYLE={background=#ffa500}");

endcomp;

compute Mapping_Notes;

call define(_COL_,"style","STYLE={background=#FFFF00}");

endcomp;

compute Variable_Use_Flag;

call define(_COL_,"style","STYLE={background=#FFFF00}");

endcomp;

compute dup_name;

if dup_name = 'Y' then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
end;

endcomp;

compute dup_label;

if dup_label = 'Y' then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
call define('NAME',"style","STYLE={background=#ffa500}");
call define('LABEL',"style","STYLE={background=#ffa500}");
end;

endcomp;

compute Label_Length;

Label_Length=length(LABEL);

if Label_Length > 40 then do;
call define(_COL_,"style","STYLE={background=#ffa500}");
end;

endcomp;

compute Comments;

call define(_COL_,"style","STYLE={background=#FFFF00}");

endcomp;

run;

Re: COMPUTE BLOCK in PROC REPORT

Cynthia_sas — Wed, 24 Mar 2021 00:39:34 GMT

Hi:

I think I've spotted part of the issue.

You should find that NAME and LABEL are set to your chosen color on every row, but only the dup_label=Y rows are being set conditionally. To test this, change the colors in the NAME and LABEL compute blocks to pink or green or some other color than #ffa500 (orange). The conditional highlighting in the compute block for DUP_LABEL will only override the rows where the condition is met. If you don't want these cells highlighted differently you might just delete the compute blocks for NAME and LABEL.

Another comment, FLOW as a DEFINE statement option is ignored by ODS because it is a LISTING only option. the sub-option for flow='DATA' should be OK in ODS EXCEL, but it has nothing to do really with the FLOW option on the DEFINE statement. And I realize that you might not have shown your full code, but the STYLE(LINES) option seems to be unnecessary, since you don't have any LINE statements in your code.

Hope this helps,

Cynthia

Re: COMPUTE BLOCK in PROC REPORT

cgsmoak56 — Wed, 24 Mar 2021 16:04:55 GMT

Thank you, Cynthia! Your solution worked.