Based on the code below I am labeling my field names. I now want to highlight the entire column if it is the current month, how can I accomplish this?
Code to get my months:
select distinct
catt('WORK.AllTotals(where=(Month="',Month,'")
rename=(Open_Dollars_rd=',Month,'_Open_Dollars_rd Shipped_Dollars_rd=',Month,'_Shipped_Dollars_rd Project_Dlrs_rd=',Month,'_Project_Dlrs_rd))')
into :
list separated by ' '
from WORK.AllTotals
I tried to attach, but my files too big, so just imagine these are columns with data in excel
Here's what I have;
AprPlan AprOpen AprShip AprProjected AprDiff MayPlan MayOpen MayShip MayProjected MayDiff JunPlan JunOpen JunShip JunProjected JunDiff
If current month is April, this is what I want (again, entire columns would be highlighted, not just the field name)
AprPlan AprOpen AprShip AprProjected AprDiff MayPlan MayOpen MayShip MayProjected MayDiff JunPlan JunOpen JunShip JunProjected JunDiff
If current month is May, this is what I want (again, entire columns would be highlighted, not just the field name)
AprPlan AprOpen AprShip AprProjected AprDiff MayPlan MayOpen MayShip MayProjected MayDiff JunPlan JunOpen JunShip JunProjected JunDiff
If current month is June, this is what I want (again, entire columns would be highlighted, not just the field name)
AprPlan AprOpen AprShip AprProjected AprDiff MayPlan MayOpen MayShip MayProjected MayDiff JunPlan JunOpen JunShip JunProjected JunDiff
I do not think you can implement this type of highlighting with PROC SQL. So depending on how you are getting your output from SAS to Excel, it will make a difference. For example, PROC REPORT? PROC PRINT? PROC TABULATE? And, how are you getting output into Excel: PROC EXPORT, LIBNAME engine, or using ODS???
cynthia
Hi:
As a follow up, here's an example using SASHELP.CLASS that illustrates how to change a header cell based on the value of the cell. If you only wanted to highlight 1 cell instead of both cells (such as your scenario, you'd have to change the code right before you ran the program), but, you could build the PROC FORMAT step dynamically based on the date using SAS Macro processing or you could build the DEFINE statement dynamically based on the date using SAS Macro processing..
Cynthia
proc format;
value $gen 'M' = 'lightblue'
'F' = 'pink';
value $gcolr 'M'='red'
'F'='black';
run;
ods html file="c:\temp\Show_hilite_based_on_val.html";
proc report data=sashelp.shoes;
title '1) simple Highlight for variable that is not an across item)';
column product inventory sales returns;
define product / group;
define inventory / style(header)={foreground=red};
define sales / style(header)={foreground=cyan};
define returns / style(header)={foreground=black};
run;
proc report data=sashelp.class;
title '2) Traffic Light showing for across character var';
column age sex,height;
define age / group;
define sex / across
style(header)={background=$gen. foreground=$gcolr.};
define height / mean;
run;
ods html close;
I'm using PROC REPORT with ODS tagsets.ExcelXP to do other highlighting (when numbers are negative) and export out to excel.
You need to post a little test data, so we can test code. And use just one or two variables NOT FIVE , that would messed up and cost us much time .
My apologizes, I was just trying to make sure it was understood exactly what I was trying to accomplish. My highlighting did not come through, but in this example, if the current month was May, May Sales Plan, May Shipped Plus Projected and May Diff would be highlighted in Yellow.
Sales Rep Name | Apr Sales Plan | Apr Shipped Plus Projected | Apr Diff | May Sales Plan | May Shipped Plus Projected | May Diff | Jun Sales Plan | Jun Shipped Plus Projected | Jun Diff |
Rep 1 | $51.00 | $44.00 | $-7 | $47.00 | $43.00 | $-4 | $59.00 | $68.00 | $9.00 |
Rep 2 | $22.00 | $13.00 | $-9 | $8.00 | $10.00 | $2.00 | $17.00 | $14.00 | $-3 |
Rep 3 | $17.00 | $21.00 | $4.00 | $21.00 | $7.00 | $-14 | $27.00 | $14.00 | $-13 |
Rep 4 | $26.00 | $9.00 | $-17 | $18.00 | $7.00 | $-11 | $26.00 | $5.00 | $-21 |
Rep 5 | $11.00 | $1.00 | $-10 | $17.00 | $4.00 | $-13 | $17.00 | $7.00 | $-10 |
Rep 6 | $7.00 | $15.00 | $6.00 | $19.00 | $10.00 | $-11 | $19.00 | $11.00 | $-8 |
Rep 7 | $4.00 | $38.00 | $16.00 | $12.00 | $25.00 | $2.00 | $13.00 | $44.00 | $9.00 |
Hi:
Did you see the code I posted? How you accomplish the highlighting will depend, heavily, on how your input data are structured. You said that you are using PROC REPORT, but did not show any code. As you can see from my example code, the method you use to do the highlighting of the columns will depend on whether you are using method #1 (you have a DEFINE statement for every column) or method #2 (you are using ACROSS variables to make multiple columns). Either method 1 or method 2 can be made more dynamic with the use of SAS Macro processing, because then you could use a %IF statement to write the appropriate highlighting code. And, also seeing the PROC REPORT code would help someone to understand the structure of your data. As you can see there is a difference between the PROC REPORT in #1 and the PROC REPORT in #2 -- but it would be counter-productive to introduce macro programming into a discussion where the data is not clear and the program being used is not clear.
When you use TAGSETS.EXCELXP, you are not, technically, doing an "EXPORT" to Excel. Instead, you are creating a Spreadsheet Markup Language XML file that Excel knows how to open and render. What does your PROC REPORT code look like? What does your data look like. What you want to do is clear. How you are going to do it depends on your input data, and your PROC REPORT code?
Also, are you comfortable using SAS Macro code and writing SAS Macro programs?
cynthia
I am using a DEFINE statement. I'm very new to SAS and have never done a macro. As far as my data, In my screen shot above is the data I'm using. I have the data I want stored in a table, now I am just using this data from the table to generate the Excel spreadsheet which has multiple worksheets within. To highlight my negative amounts I am using the following code (If there is a better way, please let me know! ) I have the following statement for each of the 'Diff' colunms (Apr Diff, May Diff, Jun Diff)
COMPUTE SUM_of_Apr_Diff;
if SUM_of_Apr_Diff < 0 and SUM_of_Apr_Diff ne '.' THEN
CALL DEFINE('SUM_of_Apr_Diff', 'Style', 'STYLE=[BACKGROUND=Red]');
endcomp;
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.