PROC SGPLOT - Add a 2nd variable to a 2nd Y axis

[_maldini_]

How do I add a new variable and a new Y axis to a line graph using PROC SGPLOT?

The syntax at the bottom of this post produces this line graph: 

I am outputting percentages from a PROC FREQ and then graphing the percentage over time, by a grouping variable (i.e., group = cann_use_status) using a format:

VALUE 
cann_use_statusfmt
1 = "Never"
2 = "Past Use"
3 = "Current Use, Light (< 5d/past mo)"
4 = "Current, Heavy (≥ 5d/past mo)";

I want a graph that looks like this (see pink dotted line)

In this example, the pink line is the grouping variable, but with a different format (see below). But...I would like to learn how to add any variable to a second axis (assuming the units are the same).

VALUE 
cann_use_status3fmt
1 = "Never"
2 = "Past Use"
3,4 = "Current Use";

I want to graph the column percent (i.e., the percentage of respondents in that year that identify as current users).

The 1st Y axis is the percentage reporting a prior MI (i.e., heart attack) by the grouping variable (i.e., cann_use_status). I'd like to add cann_use_status to the other Y axis (i.e., percentage of current users, independent of MI status).

Sorry for the long, complicated post. Thanks for any assistance.

PROC FREQ DATA=nhanes.go;
	where cann_use_status ne .;	
 	TABLE ever_told_mi*year*cann_use_status / outpct out=freqout;
 	format cann_use_status cann_use_statusfmt.;
run;


proc sgplot data=freqout ;
   where ever_told_mi=1;

   series x=year y=pct_row

   / lineattrs=(thickness=2px) markers DATALABEL=pct_row group=cann_use_status;
   xaxis 
   label = "Time in 2-year intervals"
   values=(2009-2010 to 2017-2018);
  
   yaxis 
   label = "Percentage Reporting Prior MI"
   values=(0 to 100 by 10)
   VALUESFORMAT=f8.1;
 	
   format 
   ever_told_mi yes_nofmt.
   cann_use_status cann_use_statusfmt.
   pct_row 8.1;
   
   title "Figure 1b. Percentage of Middle-Aged Adults Reporting Prior MI in NHANES Biennial Examinations, by Cannabis Use Status and year (2009-2020)";
run;

Create table freqout(ever_told_mi float, year varchar(9), cann_use_status float, count float, percent float, pct_tabl float, pct_row float, pct_col float);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2009-2010', 1, 145, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2009-2010', 2, 55, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2009-2010', 3, 14, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2009-2010', 4, 38, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2011-2012', 1, 134, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2011-2012', 2, 66, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2011-2012', 3, 15, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2011-2012', 4, 41, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2013-2014', 1, 156, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2013-2014', 2, 86, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2013-2014', 3, 22, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2013-2014', 4, 31, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2015-2016', 1, 126, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2015-2016', 2, 61, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2015-2016', 3, 16, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2015-2016', 4, 29, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2017-2018', 1, 114, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2017-2018', 2, 76, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2017-2018', 3, 10, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(., '2017-2018', 4, 31, ., ., ., .);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2009-2010', 1, 23, 0.1492, 9.4262, 38.3333, 27.3810);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2009-2010', 2, 28, 0.1817, 11.4754, 46.6667, 23.1405);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2009-2010', 3, 3, 0.0195, 1.2295, 5.0000, 25.0000);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2009-2010', 4, 6, 0.0389, 2.4590, 10.0000, 22.2222);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2011-2012', 1, 10, 0.0649, 4.0984, 28.5714, 11.9048);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2011-2012', 2, 21, 0.1362, 8.6066, 60.0000, 17.3554);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2011-2012', 3, 1, 0.0065, 0.4098, 2.8571, 8.3333);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2011-2012', 4, 3, 0.0195, 1.2295, 8.5714, 11.1111);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2013-2014', 1, 15, 0.0973, 6.1475, 30.0000, 17.8571);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2013-2014', 2, 26, 0.1687, 10.6557, 52.0000, 21.4876);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2013-2014', 3, 4, 0.0260, 1.6393, 8.0000, 33.3333);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2013-2014', 4, 5, 0.0324, 2.0492, 10.0000, 18.5185);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2015-2016', 1, 19, 0.1233, 7.7869, 41.3043, 22.6190);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2015-2016', 2, 23, 0.1492, 9.4262, 50.0000, 19.0083);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2015-2016', 3, 2, 0.0130, 0.8197, 4.3478, 16.6667);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2015-2016', 4, 2, 0.0130, 0.8197, 4.3478, 7.4074);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2017-2018', 1, 17, 0.1103, 6.9672, 32.0755, 20.2381);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2017-2018', 2, 23, 0.1492, 9.4262, 43.3962, 19.0083);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2017-2018', 3, 2, 0.0130, 0.8197, 3.7736, 16.6667);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(1, '2017-2018', 4, 11, 0.0714, 4.5082, 20.7547, 40.7407);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2009-2010', 1, 1626, 10.5489, 10.7185, 49.1982, 21.7933);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2009-2010', 2, 1323, 8.5831, 8.7212, 40.0303, 22.5383);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2009-2010', 3, 97, 0.6293, 0.6394, 2.9349, 21.3656);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2009-2010', 4, 259, 1.6803, 1.7073, 7.8366, 18.7004);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2011-2012', 1, 1415, 9.1800, 9.3276, 48.7427, 18.9653);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2011-2012', 2, 1164, 7.5516, 7.6730, 40.0965, 19.8296);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2011-2012', 3, 80, 0.5190, 0.5274, 2.7558, 17.6211);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2011-2012', 4, 244, 1.5830, 1.6084, 8.4051, 17.6173);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2013-2014', 1, 1528, 9.9131, 10.0725, 47.7948, 20.4798);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2013-2014', 2, 1300, 8.4339, 8.5695, 40.6631, 22.1465);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2013-2014', 3, 90, 0.5839, 0.5933, 2.8151, 19.8238);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2013-2014', 4, 279, 1.8100, 1.8392, 8.7269, 20.1444);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2015-2016', 1, 1562, 10.1336, 10.2966, 52.0667, 20.9355);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2015-2016', 2, 1077, 6.9872, 7.0995, 35.9000, 18.3475);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2015-2016', 3, 99, 0.6423, 0.6526, 3.3000, 21.8062);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2015-2016', 4, 262, 1.6998, 1.7271, 8.7333, 18.9170);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2017-2018', 1, 1330, 8.6285, 8.7673, 48.1013, 17.8260);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2017-2018', 2, 1006, 6.5265, 6.6315, 36.3834, 17.1380);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2017-2018', 3, 88, 0.5709, 0.5801, 3.1826, 19.3833);
Insert into freqout(ever_told_mi, year, cann_use_status, count, percent, pct_tabl, pct_row, pct_col) Values(2, '2017-2018', 4, 341, 2.2123, 2.2479, 12.3327, 24.6209);

[Reeza]

This will get you close. Not sure how Current is calculated, doesn't appear in data so I just averaged the current values and added it as a separate series. 

You specify Y2AXIS as an option on the series/scatter statement to have a secondary axis. 

You can control the legend with KEYLEGEND statement. Left that as an exercise to you.

proc means data=freqout noprint nway;
where cann_use_status in (3, 4) and ever_told_mi=1;
class year ever_told_mi;
weight count;
var pct_row;
output out=totals mean = AVG_VALUE;
run;

data freqout2;
set freqout totals;
run;

proc format;
VALUE 
cann_use_status3fmt
1 = "Never"
2 = "Past Use"
3,4 = "Current Use";

VALUE 
cann_use_statusfmt
1 = "Never"
2 = "Past Use"
3 = "Current Use, Light (< 5d/past mo)"
4 = "Current, Heavy (≥ 5d/past mo)";

run;

proc sgplot data=freqout2 ;
   where ever_told_mi=1;
   series x=year y=pct_row
   / lineattrs=(thickness=2px) markers DATALABEL=pct_row group=cann_use_status;
   xaxis 
   label = "Time in 2-year intervals";
   
   series x=year y=AVG_VALUE
   / lineattrs=(thickness=2px) markers DATALABEL=AVG_VALUE;
   y2axis 
   label = "Time in 2-year intervals";
   *values=(2009-2010 to 2017-2018);
  
   yaxis 
   label = "Percentage Reporting Prior MI"
   values=(0 to 100 by 10)
   VALUESFORMAT=f8.1;
   
      y2axis 
   label = "Percentage Reporting Current Use"
   values=(0 to 100 by 10)
   VALUESFORMAT=f8.1;
 	
   format 
   ever_told_mi yes_nofmt.
   cann_use_status cann_use_statusfmt.
   pct_row 8.1;
   
   title "Figure 1b. Percentage of Middle-Aged Adults Reporting Prior MI in NHANES Biennial Examinations, by Cannabis Use Status and year (2009-2020)";
run;

[_maldini_]

@Reeza Thanks. This helps a lot. 

A couple of clarifying questions (See image below):

1. The other Y axis is not represented on the graph. Is there a way to add it?

2. Do you know what the dotted grey line in the legend represents (i.e., how do I get rid of it. Full PROC SGPLOT syntax at the end of the post)?

3. The pink line is a good proof of concept, but it is not graphing the correct percentage (my fault, not yours). I want to graph the column percent using a different format (or in the future, a totally different nominal variable). Based on my limited knowledge, I would run a separate PROC FREQ and output a different column percent. This is my attempt to mimic your PROC MEANS approach:

PROC FREQ DATA=nhanes.go;
	where cann_use_status ne .;
 	TABLE ever_told_mi*(cann_use_status)*year / outpct out=freqout_1;
 	format cann_use_status cann_use_statusfmt.;
run;

PROC FREQ DATA=nhanes.go;
	where cann_use_status ne .;
 	TABLE ever_told_mi*(cann_use_status)*year / outpct out=freqout_3;
 	format cann_use_status cann_use_status3fmt.;
run;

data freqout_4;
set freqout_1 freqout_3;
run;

The 2nd PROC FREQ uses the different format. The problem is that the column percent (i.e., PCT_COL) has the same name in the outputted data sets (i.e., freqout_1 and freqout_3), so I can't identify it as a separate variable in the PROC SGPLOT (see image below). There is only 1 PCT_COL.

I don't see a way to specify the statistics to output in a PROC FREQ (e.g., row_pct, col_pct, etc.) like you would in a PROC MEANS (e.g., mean, max, etc.). Does it simply output everything? Also, is there a way to specify the name of an outputted variable in PROC FREQ (e.g., PCT_COL = col_pct_2)? This would be similar to "output out=totals mean = AVG_VALUE;" in the PROC MEANS.

Thanks again.

proc sgplot data=freqout2 ;
   where ever_told_mi=1;
   
   series x=year y=pct_row
   / lineattrs=(thickness=2px) markers DATALABEL=pct_row group=cann_use_status;
   
   xaxis 
   label = "Time in 2-year intervals";
   
   yaxis 
   label = "Percentage Reporting Prior MI"
   values=(0 to 60 by 10)
   VALUESFORMAT=f8.1;
   
   series x=year y=AVG_VALUE
   / lineattrs=(thickness=4px) markers DATALABEL=AVG_VALUE 
   
   legendlabel="Percentage Reporting Current Use";
   keylegend / position=bottom ;;
   
   x2axis 
   label = "Time in 2-year intervals";
   *values=(2009-2010 to 2017-2018);
 
   y2axis 
   label = "Percentage Reporting Current Use"
   values=(0 to 60 by 10)
   VALUESFORMAT=f8.1;
	
   format 
   ever_told_mi yes_nofmt.
   cann_use_status cann_use_statusfmt.
   pct_row 8.1
   avg_value 8.1;
   
   title "Figure 1b. Percentage of Middle-Aged Adults Reporting Prior MI in NHANES Biennial Examinations, by Cannabis Use Status and year (2009-2020)";
run;

[Reeza]

---

@_maldini_ wrote:

A couple of clarifying questions (See image below):

1. The other Y axis is not represented on the graph. Is there a way to add it?

You never actually put the Y2AXIS option in your code, recheck it against my code and run it. Should have the second axis.

---

2. Do you know what the dotted grey line in the legend represents (i.e., how do I get rid of it. Full PROC SGPLOT syntax at the end of the post)?

---

I suspect that's the output from your second PROC FREQ. 

Look at the data structure on the data set I created and compare it to yours. It is not the same. You need to have a new variable in the data set, you cannot reuse the same variable on a different axis or at least it doesn't make sense in context of your question.

---

The 2nd PROC FREQ uses the different format. The problem is that the column percent (i.e., PCT_COL) has the same name in the outputted data sets (i.e., freqout_1 and freqout_3), so I can't identify it as a separate variable in the PROC SGPLOT (see image below). There is only 1 PCT_COL.

I don't see a way to specify the statistics to output in a PROC FREQ (e.g., row_pct, col_pct, etc.) like you would in a PROC MEANS (e.g., mean, max, etc.). Does it simply output everything? Also, is there a way to specify the name of an outputted variable in PROC FREQ (e.g., PCT_COL = col_pct_2)? This would be similar to "output out=totals mean = AVG_VALUE;" in the PROC MEANS.

You can rename any variable in a data set using the data set options. 

In PROC FREQ the statistics are output by default and you suppress them with options, such as norow, nopercent etc. See the PROC FREQ documentations for details.  You cannot specify the names but you can easily rename them using a data set option either on the OUT= data set or the SET statement. 

data freqout_4;
set freqout_1 freqout_3 (rename=pct_col=pct_col_y2);
run;

[_maldini_]

<You never actually put the Y2AXIS option in your code, recheck it against my code and run it. Should have the second axis.>

When I run your code it doesn't add the axis. I copied and pasted it verbatim. This is the graph (below).

[Reeza]

From this code

LABEL is not valid within the SERIES statement.

proc sgplot data=freqout2 ;
   where ever_told_mi=1;
   series x=year y=pct_row
   / markers lineattrs=(thickness=2px) group=cann_use_status DATALABEL=pct_row; ;
   
   series x=year y=AVG_VALUE
   / markers lineattrs=(thickness=2px)  y2axis DATALABEL=AVG_VALUE;

   *values=(2009-2010 to 2017-2018);
  
    yaxis 
   label = "Percentage Reporting Prior MI"
   values=(0 to 100 by 10)
   VALUESFORMAT=f8.1;
   
     y2axis 
   label = "Percentage Reporting Current Use"
   values=(0 to 100 by 10)
   VALUESFORMAT=f8.1;
 	
   format 
   ever_told_mi yes_nofmt.
   cann_use_status cann_use_statusfmt.
   pct_row 8.1;

run;