DATA Step, Macro, Functions and more

Naming a variable after a Proc Transpose

Reply
Contributor CLE
Contributor
Posts: 24

Naming a variable after a Proc Transpose

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
Super User
Posts: 23,937

Re: Naming a variable after a Proc Transpose

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.

Super User
Posts: 2,492

Re: Naming a variable after a Proc Transpose

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

 

 

Valued Guide
Posts: 624

Re: Naming a variable after a Proc Transpose

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.

Super User
Posts: 13,876

Re: Naming a variable after a Proc Transpose

Posted in reply to andreas_lds

@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;
Ask a Question
Discussion stats
  • 4 replies
  • 86 views
  • 0 likes
  • 5 in conversation