BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
craig159753
Quartz | Level 8

Hi all,

 

I have produced an EXCEL output through SAS EG, I want to colour some of the cells YELLOW when another column has a certain value. Below is stripped down version of the data set

 

OBS        VALUE                     MATCH

1              Smith                           Y

2              Jones                          N

3              Edwards                      N

4              Adams                         Y

 

So the EXCEL output looks almost identical to the SAS data set, other than I DO NOT want to the print the MATCH column. I just need the VALUE column to be highlighted YELLOW when the MATCH column contains the letter N.

 

My code so far

 

ods listing close;
ods tagsets.excelxp file="\\NA1SASFILE1\CVSSandbox\&sysuserid.\AMG\COMPARE\compare.xls" 
style = sasweb
options (sheet_interval = "none" sheet_name = "SUBJECT"); proc report data = compare_t5 nowd style(report)={background=white} style(header)={background=lightblue} style(column)={background=white}; column observation value match /* Standard Columns Columns */ define observation / display order=internal; define value / display order=internal; define match / noprint; /* Conditional Formatting */ compute value; if match = "N" then call define(_col_,'style','style={background=yellow}'); endcomp; run; ods tagsets.excelxp close; ods listing;

I can not use the format method because my data set contains thousands of records. And not all "Smiths" for example will need to be highlighted yellow.

 

any help would be great. 

1 ACCEPTED SOLUTION

Accepted Solutions
Tim_SAS
Barite | Level 11

Remember, PROC REPORT builds each row from left to right, so in your example the value of 'match' is not known when 'value' is computed. To change the style of 'value' based on the value of 'match', the 'match' column must be to the left of the 'value' column. 

 

See these examples.

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Simplest way I can see, have a datastep which appears before the proc report.  This datastep would flag the data you need to change the color of and assign a color value:

data your_data;
  set your_data;
  if ... then flag=1;
  if ... then flag=2;
...
run;

Then in your proc report use the flag variable to calculate wether a row should be colored and what color:

proc report data=your_data;
 ...
  compute value;
    if flag=1 then call define(_col_,'style','style={background=yellow}');
    if flag=2 then call define(_col_,'style','style={background=red}');
  endcomp;
run;
Tim_SAS
Barite | Level 11

Remember, PROC REPORT builds each row from left to right, so in your example the value of 'match' is not known when 'value' is computed. To change the style of 'value' based on the value of 'match', the 'match' column must be to the left of the 'value' column. 

 

See these examples.

craig159753
Quartz | Level 8

Spot on! I realised this earlier, but thanks for solidifying it!!

Cynthia_sas
SAS Super FREQ
Hi:
It's not a great idea to append your new question to an already solved posting. And you've already asked this question in a different posting, haven't you? Isn't this the same question:
https://communities.sas.com/t5/General-SAS-Programming/PROC-REPORT-compare-col-vs-col/m-p/372100#M47...

And the same "left-to-right" rule of PROC REPORT applies.

Please go back and read the other posting. The correct answer has been posted several times.

cynthia
srinidelite
Obsidian | Level 7

Apologies   😞 

 

Yes I will Move this question back to the mentioned post ! Thanks again for regulating me 🙂

 

 

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
  • 8327 views
  • 1 like
  • 5 in conversation