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 | |||
FY18 | FY18YTD | FY19YTD | |
ABC | 700 | 750 | 532 (colored green) |
DEF | 490 | 490 | 380 (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.
Product | FISC_YEAR_NBR_LABEL | AvgDaily | |
ABC | FY18 | 700 | |
DEF | FY18YTD | 490 | |
ABC | FY18YTD | 750 | |
ABC | FY19YTD | 532 | |
DEF | FY19YTD | 380 | |
DEF | FY18 | 490 | |
Split Columns to be like this. | |||
Product | FY18 | FY18YTD | FY19YTD |
ABC | 700 | 750 | 532 |
DEF | 490 | 490 | 380 |
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.
You could name your variables THIS_FY, THIS_FYTD, NEXT_FYTD and only assign labels dynamically.
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.
@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;
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.
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.