I have a list of 20-30 factors which I would like to create 4 graphs each for. Ideally I would like them in an excel file, each on a separate sheet (four graphs per sheet) but I know that's next too impossible. Exporting to powerpoint and pdf are sufficient alternatives. What I like about Excel is that you can separate each factor into its own workbook. I have started the process off using robslink.com as a reference.
My code is below (which I took from http://www.robslink.com/SAS/democd7/clin1_info.htm)
Couple of points:
My main challenges are:
data my_data;
INFILE DATALINES DLM=',';
format factor level $16.;
input factor $ level $ exposure lossratio ;
datalines ;
occupation,professional,500,.44
occupation,student,1000,.55
occupation,retired,2000,.75
;
run;
data my_data; set my_data;
length htmlvar $500;
htmlvar=
'title='||quote(
'Level: '|| trim(left(level)) ||'0D'x||
'Vehicle Exposure Years: '|| trim(left(v01_veh_exp_years))||
'Loss Ratio: '|| trim(left(all_LR)))||
' href="clin1_info.htm"';
run;
goptions device=png;
goptions xpixels=750 ypixels=500;
goptions noborder;
ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm"
(title="SAS/Graph gbarline Line & Column Chart") style=sasweb;
goptions gunit=pct htitle=6 ftitle="albany amt/bold" htext=4.25 ftext="albany amt/bold";
title1 ls=1.5 "&name. - LR";
/*title2 "Classic Combination Chart";*/
pattern1 v=solid color=cx993366;
symbol1 interpol=join width=3 value=dot height=4 color=navy;
axis1 label=(a=90 'Exposure Years') offset=(0,2) order=(0 to 30000 by 5000) major=(h=-1) minor=none;
axis2 label=(a=90 'Loss Ratio') offset=(0,2) order=(0 to 2 by .5) major=(h=-1) minor=none;
axis3 label = none value=(angle=90) offset=(13,13) ;
device=pdfc
ftext="swissb";
ods pdf file="H:\Commercial\Fleet\Presentations\four in one.pdf" startpage=never;
ods proclabel "Four graphs on one page" ;
goptions hsize=5.20 in vsize=3.75 in;
title1;
proc gbarline data=my_data2;
goptions horigin=0in vorigin=3.99 in;
bar level / discrete
type=sum sumvar=v01_veh_exp_years
axis=axis1 maxis=axis3
space=5 width=6
html=htmlvar
des='' name="&name" ;
plot /
type=sum sumvar=all_LR
axis=axis2
html=htmlvar;
run;
quit;
proc gbarline data=my_data2;
goptions horigin=5.25;
bar level / discrete
type=sum sumvar=v01_veh_exp_years
axis=axis1 maxis=axis3
space=5 width=6
html=htmlvar
des='' name="&name" ;
plot /
type=sum sumvar=all_LR
axis=axis2
html=htmlvar;
run;
quit;
proc gbarline data=my_data2;
goptions hsize= 5.20in vsize= 3.75in device=pdfc ftext="swissb"
bar level / discrete
type=sum sumvar=v01_veh_exp_years
axis=axis1 maxis=axis3
space=5 width=6
html=htmlvar
des='' name="&name" ;
plot /
type=sum sumvar=all_LR
axis=axis2
html=htmlvar;
run;
quit;
proc gbarline data=my_data2;
goptions horigin=5.25 in;
bar level / discrete
type=sum sumvar=v01_veh_exp_years
axis=axis1 maxis=axis3
space=5 width=6
html=htmlvar
des='' name="&name" ;
plot /
type=sum sumvar=all_LR
axis=axis2
html=htmlvar;
run;
quit;
ods pdf close;
I think the hard part is going to be how to get your data in the format for the plots.
Here's one way using GTL:
data have;
input factor $ level no_of_observations exposure Ratio1 Ratio2 value1 value2;
cards;
factor1 1 500 1000 105 35 2000 348
factor1 2 1000 2000 82 25 3000 200
factor1 3 20000 3000 80 22 2000 320
factor1 4 23000 500 76 21 1000 430
factor1 5 22333 8000 86 40 3500 1200
;
run;
proc template;
define statgraph fourInOne;
begingraph;
entrytitle '4 graphs on one page';
layout gridded/ rows=2 columns=2;
layout overlay;
seriesplot x=level y=exposure;
endlayout;
layout overlay;
seriesplot x=level y=ratio1;
endlayout;
layout overlay;
seriesplot x=level y=ratio2;
endlayout;
layout overlay;
seriesplot x=level y=value1;
endlayout;
endlayout;
endgraph;
end; run;
proc sgrender data=have template=fourInOne;
run;
And here's another using ODS PDF Columns option and basically manually sizing the graphs so it fits 4 to a page. But this makes it easier if you have different plots required for each quadrant and easier to modify in my opinion.
options orientation=landscape nodate nonumber;
ods pdf file='C:\temp\sample.pdf' columns=2 style=journal startpage=no;
ods graphics on/width=4in height=3in;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "IBM"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "Intel"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "Microsoft"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "IBM"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
ods graphics off;
ods pdf close;
factor | level | policy_years | Loss Ratio | Freq | Sev | Claim No's | Total Clm Amt | Prem | exp_yrs_2008 | exp_yrs_2009 | exp_yrs_2010 | exp_yrs_2011 | exp_yrs_2012 | exp_yrs_2013 | exp_yrs_2014 | An example of my dataset (for one factor) is shown below:
Age | Old | 6000 | 76% | 60% | 4000 | 1000 | 6400000 | 4000000 | 500 | 1,000 | 1,500 | 750 | 500 | 500 | 1,250 |
Age | New | 20000 | 54% | 50% | 3250 | 4500 | 2500000 | 8000000 | 1,500 | 3,000 | 4,500 | 2,250 | 1,500 | 1,500 | 5,750 |
However, is it possible to incorporate color into the graphs and control the position of the legend?
Yes, change the style in the ODS PDF statement. For example, Meadow and Seaside are two styles I like. You can also customize your own styles but that's a lot of work. The legend can be customized in the PROC SGPLOT code.
Can I also change the size of the graphs so there is less of a gap between each graph (I will be using for a presentation).
Yes, see the size is set at the top of the code using the width/height option, I've set it to 3/4 inches just to get 4 graphs on the page, but you will need to customize it to suit your needs.
Is it possible (either directly or indirectly) to export each page to a slide in Powerpoint?
In SAS 9.4 yes, in SAS 9.3, no.
Similarly, is it possible to export each graph to a sheet in an excel workbook?
Not AFAIK in SAS 9.3, yes in SAS 9.4. You can get all graphs into one sheet using ODS MSOFFICE_2K destination.
What you can do is generate the PNG files - image files and then place them where ever you want, in PDF, PowerPoint and Excel.
You will see that the 4th graph is a different to the others i.e. no bars just lines. Can such a graph be factored into the code above?
Yes, because each proc sgplot is independent the graphs can be different and can use different source data, I think you'll need a different data structure for your last graph so this method will work better as well. You just need to customize the SGPLOT for the graph you want.
Thanks Reeza, how would you adjust the code above to create png file for each factor and is there a way of automating the process of importing them to powerpoint?
In the code above, the graphs lose their colour when exporting to pdf - is there a way to avoid this?
Finally, how would you adjust the above code to include one factor on each page in the pdf?
Thanks
Matthew
how would you adjust the code above to create png file for each factor
Write a macro
is there a way of automating the process of importing them to powerpoint?
I'm not sure about this
the graphs lose their colour when exporting to pdf
No, they're set to not have color, change the STYLE in the ODS PDF statement, as mentioned above.
how would you adjust the above code to include one factor on each page in the pdf?
Remove the columns=2 from the ODS PDF statement and change the size. This is the default behaviour so you should get this by simply using the ODS PDF destination without any additional options.
This will generate the images as PNG files as well in the C:\Temp folder.
options orientation=landscape nodate nonumber;
ods listing gpath='C:\temp';
ods pdf file='C:\temp\sample.pdf' style=meadow;
ods graphics on/width=7in height=5in ;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "IBM"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "Intel"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "Microsoft"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d
and date <= "01jan2001"d
and stock = "IBM"));
title "Stock Volume vs. Close";
vbar date / response=volume;
vline date / response=close y2axis;
run;
ods graphics off;
ods pdf close;
You can probably bulk insert the images into your powerpoint presentation using the photo album method.
Thanks Reeza
For the fourth graph in the above I've included a proc transpose so that I can achieve the fourth graph.
As an example,
exposureyear | factor | level | exposure |
2008 | occupation | Manufacturing | 760 |
2009 | occupation | Manufacturing | 171 |
2010 | occupation | Construction | 857 |
2010 | occupation | Manufacturing | 137 |
2010 | occupation | Warehouse | 547 |
2011 | occupation | Construction | 985 |
2011 | occupation | Manufacturing | 718 |
2011 | occupation | Warehouse | 21 |
2012 | occupation | Construction | 361 |
2012 | occupation | Manufacturing | 398 |
2012 | occupation | Warehouse | 617 |
2013 | occupation | Construction | 896 |
2013 | occupation | Manufacturing | 679 |
2013 | occupation | Warehouse | 490 |
2014 | occupation | Construction | 494 |
2014 | occupation | Manufacturing | 576 |
2014 | occupation | Warehouse | 705 |
to this dataset
exposureyear | _NAME_ | Manufacturing | Construction | Warehouse |
2008 | exposure | 0 | ||
2009 | exposure | 0 | ||
2010 | exposure | 163 | 240 | 9 |
2011 | exposure | 1474 | 781 | 11 |
2012 | exposure | 1169 | 804 | 11 |
2013 | exposure | 795 | 1122 | 27 |
2014 | exposure | 488 | 996 | 29 |
Then a graph is created using the code you recommended earlier
proc sgplot data=Egidwh_oneway4 (where=(
factor = "&factor"));
title "&factor - Severity";
vline level/ response=manufacturing;
vline level / response=Construction;
vline level / response=Warehouse;
run;
Now the only problem is that I have 70 or so factors. Creating the transposed dataset for each is fine, the only issue is that the number of levels (and names) vary by factor.
So I would need some sort of macro/looping procedure that counts the number of unique levels and creates the graph with the number of graphs equal to the number of levels in the factor.
If it was possible (and this would be ideal) I would like to limit the number of graphs to 7 by including the top 7 levels (in terms of total exposure years over the period 2008 to 2014).
Can this be done??
Yes it's all possible, but I don't want to write the code
I think you can the GROUP=level and then the response as exposure instead of transposing the data to get multiple lines on a single graph.
I think you need to try some of this and then come back with more specific questions.
I understand but to be honest, I don't know where to start... I think I get the program to work out the number of unique levels in each factor but I don't know how to incorporate this information to work graph based on varying numbers in each level. Say, the number of levels is 4 for one factor and 7 for the other, could you give some guidance as to how you would allow for this in this part of the proc sgplot procedure.
4 levels
vline level/ response=level1;
vline level / response=level2;
vline level / response=level3;
. vline level / response=level4;
4 levels
vline level/ response=level1;
vline level / response=level2;
vline level / response=level3;
. vline level / response=level4;
vline level / response=level5;
vline level / response=level6;
vline level / response=level7;
I don't know how you would code to allow varying numbers of levels in the proc sgplot procedure.
I think you can the GROUP=level and then the response as exposure instead of transposing the data to get multiple lines on a single graph.
You have your data in the wrong structure. Use the GROUP= option on a VLINE statement instead then it doesn't matter how many levels you have.
VLINE level/group=level response=measure;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.