BookmarkSubscribeRSS Feed
brophymj
Quartz | Level 8

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;

12 REPLIES 12
Reeza
Super User

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?

Reeza
Super User

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;



Reeza
Super User

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;

brophymj
Quartz | Level 8
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:
factorlevelpolicy_yearsLoss RatioFreqSevClaim No'sTotal Clm AmtPremexp_yrs_2008exp_yrs_2009exp_yrs_2010exp_yrs_2011exp_yrs_2012exp_yrs_2013exp_yrs_2014
AgeOld600076%60%4000100064000004000000                    500                1,000                1,500                    750                    500                    500                1,250
AgeNew2000054%50%3250450025000008000000                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.
Reeza
Super User

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.

brophymj
Quartz | Level 8

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

Reeza
Super User
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;

Reeza
Super User

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

Create and share a photo album

brophymj
Quartz | Level 8

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,

exposureyearfactorlevelexposure
2008occupationManufacturing760
2009occupationManufacturing171
2010occupationConstruction857
2010occupationManufacturing137
2010occupationWarehouse547
2011occupationConstruction985
2011occupationManufacturing718
2011occupationWarehouse21
2012occupationConstruction361
2012occupationManufacturing398
2012occupationWarehouse617
2013occupationConstruction896
2013occupationManufacturing679
2013occupationWarehouse490
2014occupationConstruction494
2014occupationManufacturing576
2014occupationWarehouse705

to this dataset

                                                                               
exposureyear_NAME_ManufacturingConstructionWarehouse
2008exposure0
2009exposure0
2010exposure1632409
2011exposure147478111
2012exposure116980411
2013exposure795112227
2014exposure48899629

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

Reeza
Super User

Yes it's all possible, but I don't want to write the code Smiley Happy

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.

brophymj
Quartz | Level 8

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.

Reeza
Super User
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;




sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1978 views
  • 0 likes
  • 2 in conversation