topic Re: Naming a variable after a Proc Transpose in SAS Programming

Naming a variable after a Proc Transpose

CLE — Mon, 16 Jul 2018 21:17:40 GMT

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

Re: Naming a variable after a Proc Transpose

Reeza — Mon, 16 Jul 2018 21:37:03 GMT

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.

Re: Naming a variable after a Proc Transpose

ChrisNZ — Tue, 17 Jul 2018 03:05:26 GMT

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

Re: Naming a variable after a Proc Transpose

andreas_lds — Tue, 17 Jul 2018 06:12:32 GMT

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.

Re: Naming a variable after a Proc Transpose

ballardw — Tue, 17 Jul 2018 17:02:12 GMT

@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;