BookmarkSubscribeRSS Feed
LisaSAS
Obsidian | Level 7

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 Smiley Happy

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ


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

Cynthia_sas
SAS Super FREQ

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;

LisaSAS
Obsidian | Level 7

I'm using PROC REPORT with ODS tagsets.ExcelXP to do other highlighting (when numbers are negative) and export out to excel. 

Ksharp
Super User

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 .

LisaSAS
Obsidian | Level 7

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 PlanApr Shipped
  Plus Projected
Apr DiffMay Sales PlanMay Shipped
  Plus Projected
May DiffJun Sales PlanJun 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
 
Cynthia_sas
SAS Super FREQ

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

LisaSAS
Obsidian | Level 7

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! Smiley Happy)  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;

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
  • 7 replies
  • 1426 views
  • 0 likes
  • 3 in conversation