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

Hi experts,

I have following code:

 

proc import out= info
            datafile= "H:\Miscellaneous\test1.xls"
            dbms = xls replace;
            getnames= yes;
run;
ods noresults;
ods listing close;
ods excel file = "H:\Miscellaneous\Test.xlsx"
      options(
			sheet_name= "INFORMATION"
			autofilter= "ALL"
			       );
proc report data= info nowd split= '#';
     column  name stdid subject stream category enrolled_year 
     citizen_or_non_citizen Comments_Mandatory_Existing_Law;             
     define  name /order;
     define  name /'NAME';
	 define  stdid /'STUDENT ID'; 
     define  subject / 'SUBJECT';
     define  stream/'STREAM';
     define  category/ 'CATEGORY';
     define  enrolled_year/ 'ENROLLED#YEAR';
     define  citizen_or_non_citizen /'CITIZEN OR #NON CITIZEN';
     define  Comments_Mandatory_Existing_Law/'COMMENTS#MANDATORY#EXISTING_LAW'; 
    compute stream;
	if  stream ='Science'
   	then call define(_col_, "style", "style=[backgroundcolor=H0F0BFBB]");
    if  stream ='Humanities'
   	then call define(_col_, "style", "style=[backgroundcolor=H0C7809B]"); 
	endcomp;

compute Comments_Mandatory_Existing_Law;
if Comments_Mandatory_Existing_Law='Citizen - Can work'
then call define (_col_, "style", "style=[backgroundcolor=H0F0BFBB]");
if Comments_Mandatory_Existing_Law='Limited work permit for summer'
then call define (_col_, "style", "style=[backgroundcolor=H0B480FF]");
endcomp; run; ods _all_ close;

I got this report via proc report and ODS.

inquistive_0-1659988606377.png

 

 

I now want: 

1.Different color for each line in column label as follows:

 

                                   COMMENTS

                                    MANDATORY

                                    EXISTING LAW

 

2.Color of the text under 'Citizen or Non Citizen' column as follows:

                                 Citizen

                                 Non-Citizen
How can I do this? 

Thanks for your help in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
Diamond | Level 26

Hi:

  It is possible to do what you want to do. The tradeoff is that you need to use ODS ESCAPECHAR for your multi-colored header and so I had to make some fake data using SASHELP.PRDSALE because you didn't post any data to use with your code. I didn't bother mocking up your entire example, but I did use your headers and at least re-created a few of your values so the format would work. I used macro variables for the multi-colored header just because I prefer to have each line of the string as a different macro variable for ease of changing and not messing up brackets.

Cynthia_sas_0-1660008379109.png

The colors aren't exactly the ones you showed, but the basic method would be the same no matter what the color is. I really prefer the PROC FORMAT method for making the background and foreground color changes because it is far easier to change a format than to keep adding to the COMPUTE block and using CALL DEFINE.

Cynthia

View solution in original post

2 REPLIES 2
Cynthia_sas
Diamond | Level 26

Hi:

  It is possible to do what you want to do. The tradeoff is that you need to use ODS ESCAPECHAR for your multi-colored header and so I had to make some fake data using SASHELP.PRDSALE because you didn't post any data to use with your code. I didn't bother mocking up your entire example, but I did use your headers and at least re-created a few of your values so the format would work. I used macro variables for the multi-colored header just because I prefer to have each line of the string as a different macro variable for ease of changing and not messing up brackets.

Cynthia_sas_0-1660008379109.png

The colors aren't exactly the ones you showed, but the basic method would be the same no matter what the color is. I really prefer the PROC FORMAT method for making the background and foreground color changes because it is far easier to change a format than to keep adding to the COMPUTE block and using CALL DEFINE.

Cynthia

inquistive
Quartz | Level 8

@Cynthia_sas ,

Thank you Cynthia. 

It worked well.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 928 views
  • 1 like
  • 2 in conversation