topic Re: Issues creating & exporting graphs in sas in Graphics Programming

Issues creating & exporting graphs in sas

brophymj — Mon, 02 Mar 2015 22:40:48 GMT

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:

I am trying to get one factor right so I have just created a dataset with one factor and dummy values.
I want 4 graphs but using the same graph here 4 times for illustrative purposes
In the reference above, the first few lines of code where he defines htmlvar, I'm not sure what this does.

My main challenges are:

In the link above he creates a bar chart with a line chart on a secondary graph and retains with a "MS excel look". I can reproduce a single graph but I need guidance on how to create 4 on a single page, that are the same size. I'm not sure what the code highlighted in red does.
I want the code to be flexible so that it can handle loads of different factors with different ranges of levels, some numeric (age) and some character (occupation).
The code highlighted in pink is not from the above link. I took it from a different source about exporting 4 graphs to a pdf. it doesn't work properly for me (graphs appear on a pdf but they don't have the format created by robslink code and they are not fitted correctly on the page.

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;

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 01:59:02 GMT

Would SGPanel work or do you want 4 separate graphs on a single page?

EDIT:

Found the answers

What version of SAS do you have? SAS 9.3

What does your sample data look like?

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 02:33:10 GMT

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;

endgraph;

end; run;

proc sgrender data=have template=fourInOne;

run;

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 02:34:27 GMT

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;

Re: Issues creating & exporting graphs in sas

brophymj — Tue, 03 Mar 2015 11:20:19 GMT

Hi Reeza

The second example you gave is exactly what I'm looking for so thank you for that. Just a couple of questions...

My version of SAS is 9.3 by the way.

The formatting is not that important. However, is it possible to incorporate color into the graphs and control the position of the legend? 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).

Is it possible (either directly or indirectly) to export each page to a slide in Powerpoint?

Similarly, is it possible to export each graph to a sheet in an excel workbook?

An example of my dataset (for one factor) is shown below:

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

I have also include a screenshot of the graphs for this factor I produced in 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?

Regards

Matthew

p.s. I couldn't include the screenshot but it's the same as the one in the link to my other post above.

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 15:46:20 GMT

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.

Re: Issues creating & exporting graphs in sas

brophymj — Tue, 03 Mar 2015 16:06:47 GMT

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

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 16:31:43 GMT

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;

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 16:48:52 GMT

You can probably bulk insert the images into your powerpoint presentation using the photo album method.

Create and share a photo album

Re: Issues creating & exporting graphs in sas

brophymj — Tue, 03 Mar 2015 17:28:27 GMT

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??

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 17:57:04 GMT

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.

Re: Issues creating & exporting graphs in sas

brophymj — Tue, 03 Mar 2015 18:09:04 GMT

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.

Re: Issues creating & exporting graphs in sas

Reeza — Tue, 03 Mar 2015 18:36:41 GMT

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;