Graph Output in Excel - suppressing additional output

[deleted_user]

I'm sending Graph output to an excel spreadsheet. I'm able to do this using tagsets but am also getting a dump of all the data that is used to build each graph. I'm also getting a lot of verbage underneath each graph:

"The information for this graph was generated for a by-group of id=4.
This graph is a 3-D vertical bar chart of 'NAME OF FORMER VARIABLE', with a group variable of 'pd_pool', and a sum response variable of 'Difference'."

And so on. Is there a way I can suppress all this extra output? I only want the graphs to be displayed under my proc report output.

Hope someone can help.

[Cynthia_sas]

Hi:
Please send a sample of your method of invocation and file creation...for example...
[pre]
ods html file='xxxyyy.html';
proc report; run;
proc gchart; run;
ods html close;
[/pre]
OR
[pre]
ods msoffice2K file='zzzyyy.html';
proc report; run;
proc gchart; run;
ods msoffice2k close;
[/pre] OR [pre]
EG --> create graph --> send to Excel
[/pre]
It is very hard to figure out how to suppress what's happening without knowing how you're sending output to Excel. If you have created a custom tagset to create your output, then the tags that are being generated are being generated from inside the tagset. This may turn out to be a question for Tech Support, so you may just want to start with them, but if you post more specifics about your process, perhaps someone can help.

cynthia

[deleted_user]

Hi Cynthia,

Sorry about that - I'm using the MSOFFICE2K tagset.

ods markup body="\\filename_location..xls"
tagset=tagsets.MSOFFICE2K
style = styles.printer;

GOPTIONS xpixels=1000 ypixels=360;
GOPTIONS COLORS = (CX366C6C , CXD66F73 , CX8C1D4E , CXB2C590 , CXD65656 , CX7E562F , CX8CAB77 , CX246161 , CXD69695 , CXDEBE90 , CX8F6136 , CX97BD76 , CX689B9B , CXE1AFAF , CX324E13 , CX82A9A9);

Axis1 STYLE=1 WIDTH=1 Major = (COLOR=BLACK) MINOR=NONE;
Axis2 STYLE=1 WIDTH=1;
Axis3;
Axis3 STYLE=1 WIDTH=1;


PROC GCHART DATA=WORK.Checkout;
VBAR3D Measure / SUMVAR=difference
GROUP=prod_id
SHAPE=BLOCK
FRAME TYPE=SUM
COUTLINE=SAME
RAXIS=AXIS1
MAXIS=AXIS2
GAXIS=AXIS3
GAXIS=AXIS3
PATTERNID=MIDPOINT
LREF=1
CREF=BLACK
REF=0
;
BY description code;
format difference percent10.2;
RUN; QUIT;

ods markup close;

[Cynthia_sas]

Well, I'm not exactly sure what's happening because I can't duplicate your symptom.
When I run a test (in either SAS 9.1.3 or SAS 9.2), I do not get/see the message/information that you describe.

The only difference is that 1) I added device=actximg just because I think that looks better in Excel and
2) I changed the dataset to sashelp.prdsale (but used your graph statements with different vars) and
3) I added a proc report step.

There must be something else going on.
Here's the code I tested with. Since I can't duplicate the symptom, I'm stumped.
Unless anyone else has an idea, I think your best bet is to contact Tech Support for help.

cynthia

[pre]
proc sort data=sashelp.prdsale out=prdsale;
by division prodtype;
run;

ods markup path='c:\temp' (url=none)
body="forum_graph.xls"
tagset=tagsets.MSOFFICE2K
style = styles.printer;

proc report data=prdsale nowd;
by division prodtype;
column region actual predict;
define region /group;
define actual /sum f=dollar12.2;
define predict /sum f=dollar12.2;
rbreak after /summarize;
run;

GOPTIONS xpixels=1000 ypixels=360 device=actximg;
GOPTIONS COLORS = (CX366C6C , CXD66F73 , CX8C1D4E , CXB2C590 , CXD65656 , CX7E562F , CX8CAB77 , CX246161 , CXD69695 , CXDEBE90 , CX8F6136 , CX97BD76 , CX689B9B , CXE1AFAF , CX324E13 , CX82A9A9);

Axis1 STYLE=1 WIDTH=1 Major = (COLOR=BLACK) MINOR=NONE;
Axis2 STYLE=1 WIDTH=1;
Axis3;
Axis3 STYLE=1 WIDTH=1;


PROC GCHART DATA=prdsale;
VBAR3D region / SUMVAR=actual GROUP=product
SHAPE=BLOCK FRAME TYPE=SUM COUTLINE=SAME
RAXIS=AXIS1 MAXIS=AXIS2 GAXIS=AXIS3 GAXIS=AXIS3
PATTERNID=MIDPOINT LREF=1 CREF=BLACK REF=0 ;
BY division prodtype;
format actual dollar12.2;
RUN;
QUIT;

ods markup close;
[/pre]

[deleted_user]

Hello Cynthia, Thanks for checking this out. I ran your exact code and I'm getting the same issue with the extra verbage and data table as follows:


Click for description of VBAR3D chart of REGION

The information for this graph was generated for a by-group of Division=CONSUMER Product type=FURNITURE.

This graph is a 3-D vertical bar chart of 'Region', with a group variable of 'Product', and a sum response variable of 'Actual Sales'.

The following table contains the data from the graph:

Region Product Actual Sales (sum)
EAST BED $37,608.00
WEST BED $36,092.00
EAST SOFA $34,962.00
WEST SOFA $40,117.00


I'll check with Tech Support.

Rup

[deleted_user]

I've just worked through this with Martin Mincey from Tech Support. Here is his response for others who may run into a similar issue:

%gaccessible;

The GACCESSIBLE macro is a macro that we ship which allows SAS/GRAPH to produce "accessible" graphs. If you are submitting code that has the statement above in the code, simply remove the statement and re-submit your code (using a new invocation of SAS). If you are submitting your code via Enterprise Guide, you can disable the gaccessible macro by doing the following from the Enterprise Guide pulldown options menu:

Select "Tools" and then "Options". On the left hand side under "Results", select "Graph". In the "Graph" options window, disable the option at the bottom of the window that reads:

Create Accessible graphs when possible.

After disabling this option, click "OK". At this point, re-submit your code and let me know what happens. If you are using Enterprise Guide, you may have to shut down Enterprise Guide and restart it (in order for the option change to take effect).

~ I've turned off this option and I am now getting the expected output - Thanks Martin.