BookmarkSubscribeRSS Feed
deleted_user
Not applicable
hi,


I need help to obtain this kind of graph.

I have a column, ACTES_TYPE, which has 10 values.
Each ACTES_TYPE is associated with 4 different kinds of expenses incurred.


So the HBAR graph should contain ACTES_TYPE values on Y Axis, and the X Axis should contain various expenses, placed (stacked) on the same bar with different colors.
I.e. X Axis contains a single bar for each ACTES_TYPE, this single bar reflects the various expensesvalues with different colours, and lengths obviously.

i.e one bar for one ACTES_TYPE.


I am very much new to this task. Can anyone help me in this??
12 REPLIES 12
Cynthia_sas
SAS Super FREQ
Hi:
You basically have 2 choices:
1) device-based SAS/GRAPH "G" procedures, such as PROC GCHART to create a VBAR chart; or
2) template-based ODS GRAPHICS Statistical Graphics "SG" procedures

There actually is a 3rd choice -- using the GTL (graph template language) but that is probably overkill for a stacked bar chart...unless you needed to do some heavy customization.

The syntax for each of the above choices is slightly different, but clearly documented.
PROC GCHART:
http://support.sas.com/documentation/cdl/en/graphref/63022/HTML/default/viewer.htm#gchart-stmt.htm

PROC SGPLOT/VBAR:
http://support.sas.com/documentation/cdl/en/grstatproc/62603/HTML/default/viewer.htm#vbar-stmt.htm

And there are many other examples that you can find in the Tech Support notes or user group papers. The program below should serve to get you started. The program shows both methods and takes all the defaults so you can compare how the output looks in the various destinations.

You will need at least SAS 9.2 in order to run the SGPLOT code. If you do not have SAS 9.2, then you will have to use PROC GCHART to produce your stacked bar chart. Other differences between the two methods are outlined in the documentation topic shown below:
http://support.sas.com/documentation/cdl/en/graphref/63022/HTML/default/viewer.htm#a003275276.htm

cynthia
[pre]
proc sort data=sashelp.shoes out=shoes;
where region in ('Africa', 'Canada', 'Pacific') and
(product contains 'Dress' or product contains 'Casual');
by region product;
run;

ods listing close;
ods rtf file='c:\temp\compare_methods.rtf';
ods pdf file='c:\temp\compare_methods.pdf';
ods html path='c:\temp' (url=none)
file='compare_methods.html' style=analysis;
** traditional device-based graphics;
proc gchart data=shoes;
title 'PROC GCHART - VBAR';
vbar region/subgroup=product sumvar=sales;
run;
quit;

** new with SAS 9.2 template-based ODS graphics;
proc sgplot data=shoes;
title 'PROC SGPLOT - VBAR';
vbar region/ response=sales stat=sum group=product;
run;
ods _all_ close;
[/pre]
DanH_sas
SAS Super FREQ
I'll give you two options 🙂

If you have SAS 9.2, this one should work for you:
[pre]
proc sgplot data=yourdata;
hbar ACTES_TYPE / response=EXPENSES group=EXPENSE_TYPE;
run;
[/pre]

If you are using any version of SAS, you can use this:
[pre]
proc gchart data=yourdata;
hbar ACTES_TYPE / sumvar=EXPENSES subgroup=EXPENSE_TYPE;
run;
quit;
[/pre]

Hope this helps!
Dan
Cynthia_sas
SAS Super FREQ
Good catch! Somehow, I got VBAR stuck in my brain!

cynthia
deleted_user
Not applicable
Hi,


I think I didnt get, or I am not clear in my problem explaination.

My data looks like this.

ACTES TYPE E1 E2 E3 E4
-------------------------------------------------------------------------------------------------------------------

AUXILIAIRES MEDICAUX 24,715 14,702 9,811 201
BIOLOGIE, RADIOLOGIE 39,879 25,046 14,617 60
CONSULTATIONS ET VISITES 75,488 42,094 31,861 199
CURES - MATERNITE - FRAIS D'OBSEQUES 0 0 6,655 0
HOSPITALISATION 45,488 28,492 16,628 0
PETITE CHIRURGIE 8,176 4,859 3,198 11
PHARMACIE 71,596 42,267 29,339 28
PRESTATIONS DIVERSES 12,575 6,043 4,648 16


I need the ACTES TYTPE Colmn on Yaxis and the expenses, E1, E2, E3, E4 continously o nthe same bar (like series).

EX:

AUXILIAIRES MEDICAUX bar should contain all these E1, E2, E3 and E4 expenses, each with different colours.

Same for the remai ing....ACTES_TYPES...

Can you pls let me know??
Cynthia_sas
SAS Super FREQ
Hi:
You may need to consider restructuring your data, so it looks something like the example shown below. This would allow you to create the graph image you want. You could either use a DATA step program to restructure the data or you could use PROC TRANSPOSE.

Once restructured, then the syntax shown previously would work for you. Basically, you need a second category variable in order to subdivide the bar.

cynthia
[pre]
actes_type e_type expenses

AUXILIAIRES MEDICAUX E1 24715
AUXILIAIRES MEDICAUX E2 14702
AUXILIAIRES MEDICAUX E3 9811
AUXILIAIRES MEDICAUX E4 201
BIOLOGIE RADIOLOGIE E1 39879
BIOLOGIE RADIOLOGIE E2 25046
BIOLOGIE RADIOLOGIE E3 14617
BIOLOGIE RADIOLOGIE E4 60
CONSULTATIONS ET VISITES E1 75488
CONSULTATIONS ET VISITES E2 42094
CONSULTATIONS ET VISITES E3 31861
CONSULTATIONS ET VISITES E4 199
CURES - MATERNITE - FRAIS D'OBSEQUES E1 0
CURES - MATERNITE - FRAIS D'OBSEQUES E2 0
CURES - MATERNITE - FRAIS D'OBSEQUES E3 6655
CURES - MATERNITE - FRAIS D'OBSEQUES E4 0
HOSPITALISATION E1 45488
HOSPITALISATION E2 28492
HOSPITALISATION E3 16628
HOSPITALISATION E4 0
PETITE CHIRURGIE E1 8176
PETITE CHIRURGIE E2 4859
PETITE CHIRURGIE E3 3198
PETITE CHIRURGIE E4 11
PHARMACIE E1 71596
PHARMACIE E2 42267
PHARMACIE E3 29339
PHARMACIE E4 28
PRESTATIONS DIVERSES E1 12575
PRESTATIONS DIVERSES E2 6043
PRESTATIONS DIVERSES E3 4648
PRESTATIONS DIVERSES E4 16
[/pre]
deleted_user
Not applicable
hi Cynthia,


Thanks a lot for the support.
I need another support.
i am working on Z/OS environment.

i am creating a report, which contains a table and two graphs, which is to be output to an excel sheet.

I am using an HTMLpanel Tagset for this.
But, the output mainframe dataset is containg only the table, but not the graphs.

Can you pls let me know if I need to add some statements? Pls check my below code.

i am doubtful about the ODS Statements, if I am supposed to add any other options?

/*=====================STARTREPORT========================*/

%let panelborder=1;
goptions reset=all dev=HTML ;
ODS PATH(PREPEND) EXCELIB.TEMPLAT(UPDATE);
ODS TAGSETS.HTMLPANEL FILE=OUTPUT RS=NONE
style=default options(embedded_titles="yes" ) ;

;
title2 ;
title3 ;
footnote1 ;
footnote2 ;
/*=========================================================*/
ods tagsets.htmlpanel event=row_panel(start);
goptions xpixels=480 ypixels=240;
/*========================================================*/
/*======================PROC REPORT ======*/
/*=======================================================*/
options charcode ;
proc report data=outsante.SogarepR1_1002 nowd headskip split="~"
style(header)= ?< background=$bckgrnd.
...........
.........
.........
.......
.......


/*====================END OF PROC======================*/ REPORT /*=================================================*/
ods tagsets.htmlpanel event=column_panel(start);
goptions xpixels=200 ypixels=240;
/*===================PIE CHART 3D===================================*/

goptions rotate=landscape ftext="Arial" border cback='white'
device= ACTIVEX ;

title1 ;
title2 ;
title3 h=12pt f='Arial' j=C c='Dark Teal'
'Ventilation des remboursements AXA' ;
title4 ;

proc gchart data=outsante.SogarepP_1002;
format REPARTITION pct. ;
pie3d ACTES_GROUP /
discrete explode="all" noheading name="pie3d" slice=outside
coutline=blue percent=outside
sumvar=REPARTITION ; ;
quit; run;
/*====================HBAR CHART======================================*/

goptions xpixels=280 ypixels=240;
title1 "Répartition des dépenses" ;
legend1 label=none value=(j=l &val)
/* rowmajor=2 colmajor=2 */
across=2 /* down=2 */
position=(top right outside )
shape=bar(.5pt ,.5pt) ;
axis1 offset=(0,0)
order=(0 to 150000 by 15000)
label=none
;
axis2 label=none ;
proc gchart data=outsante.sogarepH2_1002;
hbar ACTES_TYPE / subgroup=FRAIS_TYPE
sumvar=FRAIS_AMOUNT
legend=legend1
autoref
coutline=darkteal
raxis=axis1
maxis=axis2
width=0.5 space=1.5
nostats
;
quit; run;
ods tagsets.htmlpanel event=column_panel(finish);
ods tagsets.htmlpanel event=row_panel(finish);
ODS TAGSETS.HTMLPANEL CLOSE; Message was edited by: Rajitha
Cynthia_sas
SAS Super FREQ
Hi:
In the future, to post code to the forum, you should avoid the use of < or > in the code. Or, if you need to use those symbols in your code, you should replace them with &lt; and &gt;
as described here:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

Also, you can use the [pre] and [/pre] tags around your code and output in order to maintain indenting and spacing.

You need to do this so that your < and > are not interpreted as HTML tags by the forum posting mechanism.

As for your problem with images and HTMLPANEL there are several factors that could be impacting your output:
1) You should see that your HTML file contains an <IMG> tag that has SRC= pointing to the location of the image file created by SAS/GRAPH
2)When you FTP your HTML file from z/OS to Windows, you have to FTP the HTML file -and- all the <IMG SRC= ...> files together.

You may want to work with Tech Support on this issue, because I do not believe that DEV=HTML is valid for use SAS FILENAME FTP, per this note:
http://support.sas.com/kb/16/110.html

In fact, I don't think you can use DEVICE=HTML with ODS at all, based on this note:
http://support.sas.com/kb/3/770.html

So you may have some incorrect underlying assumptions about the correct DEVICE driver to use with ODS. This is another reason to work with Tech Support. To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

They can look at ALL of your code and your data and your operating system configuration and give you the most help.

cynthia
Cynthia_sas
SAS Super FREQ
Sorry, because your code got truncated, I did not see the DEV=ACTIVEX later in the program. I have looked at all your code and this is what I think you are trying to do:
1) use ODS HTMLPANEL destination
2) operating system with SAS is z/OS
3) you want to move ODS HTMLPANEL output file from z/OS to a Windows machine so you can open the HTML file (with ACTIVEX output) into Excel

Is that correct???

If we move away from your somewhat lengthy code to a simpler example, as shown below, I believe that even if you get your output over to the Windows platform, you will not be happy with the look of the HTMLPANEL output when you open the HTML file into Excel. When I submit the code below on Windows and create the output, then Excel does not respect any XPIXEL or YPIXEL setting I have -- the images are very tiny.

Also, when you use ACTIVEX as the device value, you must be sure that the proper ACTIVEX controls are loaded on the Windows system...if you do not have the ActiveX controls loaded on your Windows system, this may be another reason you are not seeing the images. (see the trouble-shooting items here:
http://support.sas.com/documentation/cdl/en/graphref/63022/HTML/default/viewer.htm#a002184516.htm )

I still think you might want to work with Tech Support on this question.

cynthia

[pre]
ods listing close;

ods tagsets.htmlpanel file="graph_panel3.html" style=analysis
options(doc='help' panelcolumns='1' panelborder='0');

proc report data=sashelp.class(obs=4) nowd;
run;

goptions reset=all cback='white' device= ACTIVEX;

ods tagsets.htmlpanel options(panelcolumns='2');

ods tagsets.htmlpanel event=panel(start);
proc gchart data=sashelp.class;
pie3d age / sumvar=height type=mean;
run;
quit;


proc gchart data=sashelp.shoes;
where product contains 'Dress' or product contains 'Casual';
vbar product / sumvar=sales type=mean;
run;
quit;

ods tagsets.htmlpanel event = panel(finish);
ods tagsets.htmlpanel close;

[/pre]
DanH_sas
SAS Super FREQ
As Cynthia says, the HTMLPANEL output will not work for you in Excel; however, you can use TAGSETS.MSOFFICE2K to generate HTML output that will work well in Excel. You will not have the paneling ability, but the output should look much better.

Hope this helps,
Dan
deleted_user
Not applicable
Hi,

Thanks for ur support.

But, I am supposed to geenrate an EXCEL sheet with a Table, and two graphs.
So, do you think that we can achieve this without having paneling facility?

And, if not ACTIVEX, can you suggest any other device to be used so that I can get my output ? So that I can atleast get back to my team saying that Ihave acieved something.


As I shared with you, I am basically new to this kind of work.

Can you pls provide me some detailed notes/ docuemtation on how to use the ODS, customize the TAGSETS for desired styles, and various options available...
Cynthia_sas
SAS Super FREQ
Hi:
If you switch to ODS MSOFFICE2K, you will be creating an HTML file. The HTML file can be opened in Excel 97 or higher. The HTML file can contain a table and your 2 graphs, all on one worksheet. The only difference will be that the graphs will not be panelled -- which means that the 2 graphs would be one underneath the other, instead of side by side.

As for your other question:

Can you pls provide me some detailed notes/ docuemtation on how to use the ODS, customize the TAGSETS for desired styles, and various options available


The ODS documentation is organized primarily by destination -- so there is a section on options for ODS HTML and other ODS MARKUP destinations, there is a section on options for ODS RTF and a similar section for ODS PRINTER-family of destinations. Most of the ODS documentation can be found by starting here at the "Dictionary of ODS Language Statements" topic:
http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/a002228116.htm

If you needed to customize style attributes, such as colors and fonts (even if you were sending your HTML output to Excel) -- the appropriate place to change styles is in the STYLE template (not the TAGSET template). Again, looking in the ODS Documentation for PROC TEMPLATE, about STYLE templates is the place to start:
http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/a001020001.htm

There have been quite a few user group papers on the subject of styles for tabular output and styles for graphical output -- for both types of output, you would use a STYLE template to make changes. If you were still running SAS 9.1.3, the syntax would be slightly different for your STYLE template than it would be in SAS 9.2, but you should be able to find user-group papers about style templates in SAS 9.1.3 if that is still your version.

As far as SAS/GRAPH customization for colors and fonts, etc, remember that for traditional SAS/GRAPH procedures, like GPLOT, GCHART and GMAP, the "traditional" GOPTIONS, and PATTERN, SYMBOL, AXIS and procedure action statement options will all have an impact on the graphic output. However, for the new SG procedures and ODS GRAPHICS, the traditional methods do not work and you either modify color and font attributes directly in the SG procedure with options or by modifying the style template that you use. (If you look in the SAS/GRAPH documentation, there are quite a few topics devoted to modifying "device-based" or traditional SAS/GRAPH versus modifying "template-based" ODS GRAPHICS output.)

I'd suggest that you try out ODS MSOFFICE2K, using the simplified program below, and see whether the results -- when opened in Excel -- are acceptable.

cynthia
[pre]
ods msoffice2k file="table_graphs.html" style=analysis;

proc report data=sashelp.class(obs=4) nowd;
run;

goptions reset=all cback='white' device= ACTIVEX;

proc gchart data=sashelp.class;
pie3d age / sumvar=height type=mean;
run;
quit;


proc gchart data=sashelp.shoes;
where product contains 'Dress' or product contains 'Casual';
vbar product / sumvar=sales type=mean;
run;
quit;

ods _all_ close;
[/pre]
DanH_sas
SAS Super FREQ
Hey,

Here is a simple example of using the MSOFFICE2K tagset. Run this program and load the "example.html" output into Excel. If you have the activex control installed, you should see a bar chart, pie chart, and a table in Excel.

[pre]
ods listing close;
ods tagsets.msoffice2k file="example.html" style=listing;
goptions dev=activex;

proc gchart data=sashelp.class;
vbar age / discrete sumvar=weight type=mean;
run;
quit;

proc gchart data=sashelp.class;
pie age / discrete sumvar=height type=mean;
run;
quit;

proc print data=sashelp.class; run;
ods tagsets.msoffice2k close;
[/pre]

If you are generating your output on Z/OS, using the activex device is the simpliest way to get your entire output back, provided all of your consumers have the control installed. If that is not a guarantee, there are really three alternatives:

1) Have someone who has the control installed load the HTML files into Excel and save them as Excel (xls) files. Doing this will presist an image form of the control into the file, as well as the ability to make it live again if you have the control installed.

2) If it is possible to have your Z/OS system write output to a web-addressable location (http), it may be possible to generate HTML output with static images where the static images are written to the web location. The image locations can be web locations in the HTML, so all you would need to bring over is the HTML from Z/OS. Let me know if this is a possibility and I'll give you more info.

3) Generate the HTML using a static image and bring both the HTML file and the images back from Z/OS before doing the import.

Hope this helps,
Dan

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
  • 1256 views
  • 0 likes
  • 3 in conversation