BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

hi guys,

i need to export pie charts from SAS into an excel file.Can anyone please share a nice code for resizing charts and exporting them to excel?

Is there also a  macro code that would export the charts to a certain spot of the excel worksheet? Smiley Happy

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

If you called the named range pie_chart for example use that as your sheet name.

PROC EXPORT DATA= work.scores

  OUTFILE= 'C:\excel files\class.xls'

  DBMS=EXCEL REPLACE;

  SHEET="pie_chart";

RUN;

20923 - How REPLACE option works with PROC EXPORT and Excel files in SAS 9.1

View solution in original post

10 REPLIES 10
Reeza
Super User

I don't think there's an easy way to export a graphic to Excel. You can using ods HTML, but then your file is HTML not really Excel.

Is this a brand new excel sheet or a sheet with data in it already?

I would recommend creating the chart in Excel and then feeding the data from SAS.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

The sheet has  tables which automatically populate from the data imported  into excel from SAS.Next to each table i am supposed to have  a chart designed based on the data coming from SAS.So i have 5 tabs,one with the tables and 4 tabs with data coming from SAS.So,as data  would be coming from SAS those charts will be updated in each of the tabs and  then i manually move them in the first tab next to the tables.Is there a way maybe i can link the  charts from the 1st tab to the charts in each of the other 4 tabs so the ones in the 1st tab get updated along with the ones in the other 4  tabs? Probably SAS cant do this as you yourself said it. 

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

but then i wont be able to feed those  tabs with new data from SAS if i already have charts on them,right?

Reeza
Super User

I totally don't understand. The chart can be anywhere in the work book and link to sheets that SAS is updating. You shouldn't need to 'move' anything.

SAS can update a tab with new information if you create a named range in the template before hand and then write to that specifically.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

so probably linking the charts to the data coming from sas can be done with excel macro?

Reeza
Super User

Make a chart that is linked to a table in Excel.

Highlight the data and create a named range.

Make your export data fit the named range exactly (same column names, formatting etc)

Output your excel data to the named range (using proc export with replace option or the libname method by first dropping the table and then recreating it).

Open the excel file and the chart will show the new data.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

sorry but how do i output the data to the named range,i mean how do i link proc export to the named  range? Smiley Happy

Reeza
Super User

If you called the named range pie_chart for example use that as your sheet name.

PROC EXPORT DATA= work.scores

  OUTFILE= 'C:\excel files\class.xls'

  DBMS=EXCEL REPLACE;

  SHEET="pie_chart";

RUN;

20923 - How REPLACE option works with PROC EXPORT and Excel files in SAS 9.1

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

thanks Reeza.this is actually working.Thanks again

DBailey
Lapis Lazuli | Level 10

I think the suggested approach for this is to use the SAS Add-in for Office.  Its a simple matter to then link the SAS Data range to the chart.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 10 replies
  • 2211 views
  • 1 like
  • 3 in conversation