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.
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!
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.
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.