Change variable names in legend in Proc SGPanel

[joebacon]

Hi all, 

I am having a little issue with changing the variable names in the legend of PRC SGPANEL. Ballard had a great response to a similar question explaining the power of proc format. However, my data is a little different because I apply three different variables on the Y axis from three different lines. My current code looks like this:

%macro Quarter(data,category,out,label,title);
	proc sql;
		  create table &category._&out. as select 
		  mean(I_PreResYr1stT_&category)  as Pre_Q1,
		  mean(I_PreResYr2ndT_&category)  as Pre_Q2,
		  mean(I_PreResYr3rdT_&category)  as Pre_Q3,
		  mean(I_PostResYr1stT_&category) as Pos_Q1,
		  mean(I_PostResYr2ndT_&category) as Pos_Q2,
		  mean(I_PostResYr3rdT_&category) as Pos_Q3
		  from &data
		  where TRICHOT_1YR ne . and Full_Alc_Exp = 1
		  group by TRICHOT_1YR;
		  quit;

	proc transpose data=&category._&out. out=&category._&out.;run;

	data &category._&out. ; set &category._&out. ; 
	rename COL1	=RA COL2	=UR COL3	=RNA; run;
Proc Format;
value $DS
"RA"="Resolved Abstinent"
"RNA" = "Resolved Non-abstinent"
"UR" = "Unresolved";
run;
	data &category._&out.;
		set &category._&out.;
		Quadrimester = substr(_NAME_,5,2);
		if substr(_NAME_,1,3)="Pos" then Year="Post-Resolution Year";
		if substr(_NAME_,1,3)="Pre" then Year="Pre-Resolution Year";
		drop _NAME_;
		run;

ods graphics / reset=all border=off width=650px height=400px;
title height=0.2in "&title"; 
	proc sgpanel data=&category._&out. noautolegend;
	
		panelby Year/sort=(descformat) headerattrs=(family=arial size= 16pt weight=bold) novarname;
		series x=Quadrimester y=RA/
		markers markerattrs=(symbol=circlefilled size=3pct color=green) lineattrs=(color=green thickness=4 pattern=longdash);
		series x=Quadrimester y=UR/
		markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=ShortDashDot);
		series x=Quadrimester y=RNA/
		markers markerattrs=(symbol=circlefilled size=3pct color=red) lineattrs=(color=red thickness=3 pattern=solid);

		rowaxis label="&label" labelattrs=(family=arial size=16pt weight=bold ) valueattrs=(family=arial size=16pt weight=bold);
		colaxis label=' ' labelattrs=(size=16pt ) valueattrs=(family=arial weight=bold size=16pt );	
		run;
%mend;


ods rtf  file="U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf"  gtitle bodytitle;

%Quarter(sub.newcatinflation2,TLFPL  ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
%Quarter(sub.newcatinflation2,DurInsHouse  ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Housing/Durable Goods/Insurance );

ods rtf close;

I also have tried to address this when I rename columns by renaming the columns "Resolved Abstinent", "unresolved", and "Resolved Non-abstinent" like so:

	proc transpose data=&category._&out. out=&category._&out.;run;

	data &category._&out. ; set &category._&out. ; 
	rename COL1	="Resolved Abstinent" COL2	=UR COL3	=RNA; run;

	data &category._&out.;
		set &category._&out.;
		Quadrimester = substr(_NAME_,5,2);
		if substr(_NAME_,1,3)="Pos" then Year="Post-Resolution";
		if substr(_NAME_,1,3)="Pre" then Year="Pre-Resolution";
		drop _NAME_;
		run;

ods graphics / reset=all border=off width=650px height=400px;
title height=0.2in "&title"; 
footnote justify=center "&footnote";
	proc sgpanel data=&category._&out. noautolegend;
		panelby Year/sort=(descformat) headerattrs=(family=arial size= 16pt weight=bold) ;
		series x=Quadrimester y="Resolved Abstinent"/
		markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=longdash);
		series x=Quadrimester y=UR/

However, I get errors saying:

NOTE: There were 3 observations read from the data set WORK.CONSUME_Q.
NOTE: The data set WORK.CONSUME_Q has 6 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Expecting a name.  
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: The SAS System stopped processing this step because of errors.

Is there an easy way which I can rename the legend variables? P.S. I do recognize that right now I have "noautolegend" right now. I had it on for a different graph. The results without this are the same.

[joebacon]

I found another post that showed me a work around. Though I do not think this is the best way, it seems to work.

I changed the options before I renamed the columns to options validvarname=any. Then, I was able to use spaces in my variables. From there, I added options validmemname=extend and used those same variables. Make sure to not forget the 'n' after these variables with a space. My new code reads:

	proc transpose data=&category._&out. out=&category._&out.;run;
options validvarname=any;
	data &category._&out. ; set &category._&out. ; 
	rename COL1	='Resolved Abstinent'n COL2	='Unresolved'n COL3	='Resolved Non-abstinent'n; run;
options validmemname=extend;
	data &category._&out.;
		set &category._&out.;
		Quadrimester = substr(_NAME_,5,2);
		if substr(_NAME_,1,3)="Pos" then Year="Post Resolution";
		if substr(_NAME_,1,3)="Pre" then Year="Pre Resolution";
		drop _NAME_;
		run;
options validmemname=extend;
ods graphics / reset=all border=off width=700px height=500px;
title height=0.2in "&title"; 
	proc sgpanel data=&category._&out.;
		panelby Year/sort=(descformat) headerattrs=(weight=bold) ;
		series x=Quadrimester y='Resolved Abstinent'n/
		markers markerattrs=(symbol=circlefilled size=3pct color=green) lineattrs=(color=green thickness=4 pattern=longdash);
		series x=Quadrimester y='Unresolved'n/
		markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=ShortDashDot);
		series x=Quadrimester y='Resolved Non-abstinent'n/
		markers markerattrs=(symbol=circlefilled size=3pct color=red) lineattrs=(color=red thickness=4 pattern=solid);

		rowaxis label="&label" labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
		colaxis label=' ' labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
		keylegend /valueattrs=(family= arial weight=bold Size=12 );	
		run;
%mend;

Thanks to those who looked at this!

[ballardw · Accepted solution]

First why is renaming the variable important?

Second basically SAS does not allow by default any spaces in a variable name. If you set the OPTION VALIDVARNAME=ANY; you can use non-standard names but the reference then becomes "New name"n   <= that n must immediately follow the quote, no space. You are still limited to 32 characters for the name though.

Typically if want test such as "Resolved Abstinent" to appear in output instead of COL1 the approach is to assign a label to the variable.

Consider the output of the two following proc prints:

proc print data=sashelp.class (obs=5) noobs label;
run;

proc print data=sashelp.class (obs=5) noobs label;
   label age='Age of enrollment';
run;

Notice that the column header changes for the Age variable.

So try instead of renaming that you use something like

proc sgpanel data=&category._&out. noautolegend;
   panelby Year/sort=(descformat) headerattrs=(family=arial size= 16pt weight=bold) ;
   label col1="Resolved Abstinent";
   series x=Quadrimester y=Col1/
      markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=longdash);
/* and similar Label for each y variable used*/

A serious advantage of the LABEL is that you can display much longer text than 32 characters

[joebacon]

First, thank you for taking the time to look over this and respond @ballardw . I am very appreciative.

The renaming was solely for the purpose of changing the legend.

I used a solution like you stated first with the OPTION VALIDVARNAME=ANY;. It worked quite well, but i suspected that it wasn't the best way to handle the situation. I tried to format each of the variables but felt silly after doing it.

I didn't even think to label the variables. Very smart. Would the labels appear in the legend of the sgpanel graph? I am going to try now, but will close the question as you handled it!

[ballardw]

---

@joebacon wrote:

First, thank you for taking the time to look over this and respond @ballardw . I am very appreciative.

The renaming was solely for the purpose of changing the legend.

I used a solution like you stated first with the OPTION VALIDVARNAME=ANY;. It worked quite well, but i suspected that it wasn't the best way to handle the situation. I tried to format each of the variables but felt silly after doing it.

I didn't even think to label the variables. Very smart. Would the labels appear in the legend of the sgpanel graph? I am going to try now, but will close the question as you handled it!

---

Legends will attempt to use the label of a variable by default. If no label is associated with the variable the name is used. Example:

proc sgpanel data=sashelp.class;
    panelby sex ;
    scatter x=age y=height;
    scatter x=age y=weight;
    label height="Height at age"
          weight="Weight at enrollment"
   ;
  
run;

The SAS supplied data set sashelp.class does not have labels associated with the variables so without this label statement the variable name is use.

Also you can override a variable label for almost any specific procedure that produces displayed output. So if you get a variable that had a very long label, as does occur, such as the entire text of question you could replace with something shorter in you graphs by providing a different label in the procedure code. That would not change the permanent label associated with the variable.

[joebacon]

That is quite a nuanced point that would prove exceptionally useful for me as I work with survey data quite a bit. Thank you for always going above and beyond to help us learn!