topic Re: Proc Report/Compute block question in SAS Programming

Proc Report/Compute block question

biglerc — Mon, 15 Mar 2021 21:18:04 GMT

Hi everyone,

I am fairly new to Proc Report and am struggling with a requirement. I am using ODS to output a dataset to Excel, and I need to conditionally format a cell based on the value of another variable. (My code is below).

Requirement: If the value of visnd = 1 then the FU30DALD variable should be grey for that row. The whole row shouldn't be grey, just that cell under FU30DALD.

My dataset contains all the variables I am referencing in the compute block.

I have this error in my log:

"ERROR: Invalid column specification in CALL DEFINE" with "NOTE: Argument 1 to function DEFINE(' '[12 of 100 characters shown],'style','style={backg'[12 of 27 characters shown]) at line 2 column 7 is invalid."

Any guidance is appreciated!

Current code:

proc report data=final nowd headline headskip spacing=3 split='|' missing ls=256 ps=42 ;

column dummy1 _all_ ;

define dummy1 / noprint;
define FU30DALD / display;
define visnd / display;

*if FU30 not done then grey out the FU30DALD cell;
compute dummy1;
if (visnd = 1) then do;
call define (FU30DALD,'style','style={background=lightgray}');
end;
endcomp;

run;

Re: Proc Report/Compute block question

ballardw — Mon, 15 Mar 2021 21:47:17 GMT

Does your variable FU30DALD ever have the value "12 of 100 characters shown" or have a format that would show that?

From the documentation:

CALL DEFINE (column-id | _ROW_, < ' attribute-name', value>

Required Arguments

column-id

specifies a column name or a column number (that is, the position of the column from the left edge of the report). A column ID can be one of the following:

a character literal (in quotation marks) that is the column name
a character expression that resolves to the column name
a numeric literal that is the column number
a numeric expression that resolves to the column number
a name of the form '_Cn_', where n is the column number
the automatic variable _COL_, which identifies the column that contains the report item that the compute block is attached to

Re: Proc Report/Compute block question

biglerc — Mon, 15 Mar 2021 22:04:04 GMT

Hello! Thank you for looking at my question! No, the values in the variable FU30DALD are dates in character format ($10), such as '2018-01-26'.

Re: Proc Report/Compute block question

Reeza — Mon, 15 Mar 2021 22:12:48 GMT

You need to add quotes to the variable name.

call define ("FU30DALD",'style','style={background=lightgray}');

If you're using a standard reference, like _col_, _row_ then you need to not include the quotes.

Re: Proc Report/Compute block question

biglerc — Mon, 15 Mar 2021 22:47:07 GMT

Thanks, Reeza! The errors are gone in my log now. However, the color coding is not being applied. If you have any other ideas, I'm open to tring anything 🙂

Re: Proc Report/Compute block question

Reeza — Mon, 15 Mar 2021 22:50:31 GMT

Use square brackets instead of curly?
Try changing which variable you use in COMPUTE statement as well - there's an order of operations so try COMPUTE FU30ALDUD instead of COMPUTE DUMMY1.

Sorry, I have nothing to test this with....

Re: Proc Report/Compute block question

biglerc — Mon, 15 Mar 2021 22:57:24 GMT

Thank you! I understand the limitations of your being able to troubleshoot this. I appreciate the suggestions!