BookmarkSubscribeRSS Feed
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

I have a project that produces a sgplot and it works great.  However, I'd like to write the code to be able to handle changes in the FISC_YEAR_NBR_LABEL column because over time the dates will auto change based on the day/month/year. The proc report was written with the FISC_YEAR_NBR_LABEL hard coded (see code section).  The question is how do I write code to label these with generic variable names then have a method to auto name in the report code after a Proc Transpose? Or other suggestions on how to do this so that I don't have to edit the hard code every year.

YoY Comparison
 FY18FY18YTDFY19YTD
ABC700750532 (colored green)
DEF490490380 (colored green)


proc report data=WORK.Add_UPdated_Thru nowd nocenter style(header) = [background=white color=black frame=void] style(column header) = [background=white color=black frame=void rules=none height=12] style(report) = [rules=none frame=void cellspacing=15 background=white]; col ('YoY Comparison' Label FY18 FY18YTD FY19YTD); define Label / ' '; define FY18 / display; define FY18YTD / display; define FY19YTD / display; compute FY19YTD; if FY18YTD > FY19YTD then call define (_col_,"style","style={background=mogy}"); if FY18YTD < FY19YTD then call define (_col_,"style","style={background=lightred}"); if FY18YTD = FY19YTD then call define (_col_,"style","style={background=yellow}"); endcomp; run;

 

 

Data tables before Proc Transpose and after.  Afterwards, the values for the FISC_YEAR_NBR_LABEL become the headers as in the lower chart. 

 

ProductFISC_YEAR_NBR_LABELAvgDaily 
ABCFY18700 
DEFFY18YTD490 
ABCFY18YTD750 
ABCFY19YTD532 
DEFFY19YTD380 
DEFFY18490 
    
Split Columns to be like this.   
    
ProductFY18FY18YTDFY19YTD
ABC700750532
DEF490490380
4 REPLIES 4
Reeza
Super User

I'm not sure what you mean with the transpose?
PROC TRANSPOSE has both the ID and IDLABEL to allow you to dynamically name the fields, but it seems yours would translate directly. Did you try PROC TRANSPOSE and it didn't work somehow? If so, please post the code and an example of your input data and we can advise you on how it should be structured.

ChrisNZ
Tourmaline | Level 20

You could name your variables THIS_FY, THIS_FYTD, NEXT_FYTD and only assign labels dynamically. 

 

 

andreas_lds
Jade | Level 19

My first thought was: avoid transposing, because it moves data into variable names and causes the problem. The table could be created by proc tabulate, but i have no idea how to set the background-colours.

ballardw
Super User

@andreas_lds wrote:

My first thought was: avoid transposing, because it moves data into variable names and causes the problem. The table could be created by proc tabulate, but i have no idea how to set the background-colours.


You can set the background color of a cell using a format to associate either specific values or value ranges with color values and then use a style statement to associate the format with the style element.

Not pretty, just a syntax example:

proc format library=work;
value weightcolor
low- 85= 'green'
other  = 'red'
;
run;

proc tabulate data=sashelp.class;
   class sex age;
   var weight;
   tables sex*age,
          weight*(min*{style=[background=weightcolor.]}
                  max*{style=[background=weightcolor.]}
                  mean*{style=[background=weightcolor.]}
                 )
   ;
run;

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
  • 4 replies
  • 1371 views
  • 0 likes
  • 5 in conversation