BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am tied up in knots with this problem: The variable s contains a character string indicating a specific cell (or cells) within a row as a comma-separated string. I am trying to apply a background format to cells based on this control string. The problem I am trying to solved is that the shading of an indivdual cell isn't related to the value in the cell. It's related to the intersection of the column label and the value in the variable col1.

On a related note, how can I find out what's going on inside the compute block?

Thanks,

Derek

PROC REPORT DATA=test NOWD NOHEADER;
COLUMNS dx bx s col1-col8 tempo;
DEFINE dx / ORDER NOPRINT;
DEFINE bx / ORDER NOPRINT;
DEFINE tempo / COMPUTED NOPRINT;
DEFINE s / NOPRINT;
COMPUTE tempo;
_tcnt = 1;
DO WHILE (scan(s,_tcnt,',') ne ' ';
tempo = cats('col',scan(s,_tcnt,' ,');
CALL DEFINE(tempo,'style','style={background=CXcccccc}');
_tcnt = _tcnt+1;
END;
RUN;
6 REPLIES 6
deleted_user
Not applicable
Oops.. there's a missing ENDCOMP; between the END; and the RUN; in the code I typed here. I don't get what I'm trying for when I don't have syntax errors. 🙂
Cynthia_sas
SAS Super FREQ
Hi:
So did the ENDCOMP fix your issue? Or do you still have a question?
cynthia
deleted_user
Not applicable
Oh no, I still have the problem. I can't figure out how to make CALL DEFINE recognize a specific cell in each row. I've tried to create the column name on the fly inside the compute block, but it's not working.

Thanks,

Derek
Cynthia_sas
SAS Super FREQ
Hi:
Well, here are some questions... Here's your column statement:
[pre]
COLUMNS dx bx s col1-col8 tempo;
[/pre]

I'm not sure why you even need TEMPO on the report. You want to highlight one of the 8 numbered columns, COL1-COL8, correct???

The first argument to CALL DEFINE should usually be quoted. (the exceptions are _COL_ and _ROW_ -- all other references are quoted.) So that's just one reason why the CALL DEFINE is not working. Right now, it looks like you're trying to highlight the variable TEMPO, but since TEMPO is NOPRINT, that doesn't make sense and besides, PROC REPORT would expect to see 'TEMPO' in quotes, which still doesn't make sense from what you describe.

As it describes here in the doc:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473624.htm
values for column ID (first argument to CALL DEFINE) can only be
_ROW_ (which signifies that the entire report row should have some attribute changed via CALL DEFINE) or...

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




It looks to me like you're trying to parse out what is in the S variable and then
use the number to indicate which column to highlight. You could use a character expression to append the number that you parse from S to the string 'COL', but that expression would be something like this:
[pre]
COMPUTE s;
*****some kind of loop, where I is the counter....;
if left(scan(s,i,' ,')) gt ' ' then do;
CALL DEFINE('col'||put(scan(s,i,' ,'),1.0)||'.sum',
'style','style={background=CXcccccc}');
END;
***** end of whatever loop.....;
ENDCOMP;
[/pre]

For example, in the above DO loop, let's say the value of S was 5,6. When I is 2, then the second value from the scan will be 6. So now, you want to build the CALL DEFINE (out of the character expression) as though it was:
CALL DEFINE('COL6.SUM','style','....style info');
After the expression is resolved, PROC REPORT now knows that COL6.SUM is the variable column that gets highlighted.

I show the suffix ".sum" appended, because if those variables are numeric, then the appropriate way to refer to them in the CALL DEFINE is with .SUM appended. Since I didn't know what your data looked like, I figured that one possible value for S could be:
1,2,3,4,5,6,7,8 and another could be 1,3 or 5,6,7. If you only have COL1-COL8 that need to be highlighted, you're not going to ever find variable S with a value of 1,87,43???

This is one of those instances where an example of the data and a more SPECIFIC description of what you want to see in the final report would be immensely helpful. For example, I imagine that you might have data that looks like this:
[pre]
DX BX S col1 col2 col3 col4 col5 col6 col7 col8
111 aaa 1,3 10 20 30 40 50 60 70 80
111 bbb 3,4 11 21 31 41 51 61 71 81
222 aaa 5,6 12 22 33 44 55 66 77 88

[/pre]

I didn't understand your original statement that:

The problem I am trying to solved is that the shading of an indivdual cell isn't related to the value in the cell. It's related to the intersection of the column label and the value in the variable col1.


I don't see that you're ever testing the value of the COL1 variable.

Can you explain a bit more or show some made up data and clarify whether COL1-COL8 are character or numeric?? And clarify HOW you're testing the value in COL1???

cynthia
deleted_user
Not applicable
Hi Cynthia, and thank you.

As soon as I get approval, I'm buying the book 🙂

The variable s defines what column(s) need highlighting in each row. The columns to highlight are different in each row of the report. All of that calculation is done inside of a DATA step, so the only thing I have to worry about is how to get PROC REPORT to translate the value of s and apply the formatting to the individual cell. I was thinking that I needed tempo to check each row, and I was using the variable tempo to indicate the column to highlight, but I didn't quite RTFM well enough, because it states a "Character expression", not, "a character variable") but I see that I do not need this now.

col1-col8 are all character variables.

Here's what some data may look like:

[pre]
dx bx s col1 col2 col3 col4 col5 col6 col7 col8
dx1 b1 2 0.25 6 1
dx1 b1 3 .5 10 100 21 2
dx1 b1 7,8 >8 31
[/pre]

for row 1, the background of col2 should be grayed, row 2, col 3, and row 3, col7 and col8. Missing data should not matter for the highlighting.

I've changed the compute block based on your reply, so here's the new compute block, but it's still giving me trouble:

[pre]
COMPUTE s;
_tcnt = 1;

do while(scan(s,_tcnt,',')) ne ' ');
if left(scan(s,_tcnt,',')) ne ' ' then do;
CALL DEFINE('col'||left(put(scan(s,_tcnt,','),2.0),'style','style={background_color=CXcccccc}');
_tcnt = _tcnt+1;
end;

ENDCOMP;
[/pre]
Cynthia_sas
SAS Super FREQ
Hi:
I suspect you might be having problems because "conceptually" you think of S as needing to be available to PROC REPORT -before- COL1-COL8 are changed..hence this COLUMN statement (but without TEMPO):
[pre]
COLUMNS dx bx s col1-col8 tempo;
[/pre]

But PROC REPORT works from left to right. So the compute block for variable S might not be able to issue a CALL DEFINE for other columns that have not yet been placed on the report row. You might try this if you want to have your CALL DEFINE in the COMPUTE block for S:
[pre]
COLUMNS dx bx col1-col8 s;
[/pre]

There are ways that PROC REPORT is subtly different from the DATA step and this business of COLUMN statement placement is one of them.

BTW, the new book is all about ODS with PROC REPORT mentioned in relation to ODS -- we don't go into issues of CALL DEFINE very heavily. If you really want to geek out on PROC REPORT, you should look at Art Carpenter's PROC REPORT book. 😉

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 887 views
  • 0 likes
  • 2 in conversation