topic Re: How to export proc summary to existing excel spreadsheet in SAS Enterprise Guide

How to export proc summary to existing excel spreadsheet

DME790 — Wed, 23 Mar 2016 00:15:06 GMT

Hi I'm a new user to SAS EG and I'm trying to export my proc summary out to specific worksheet in excel. I have been trying the export:

proc export data=work.CLKSuccessfulSummarybyDay

outfile='R:\NAT\SMARTCENT\SMCTRCHOP\GCFTELE\Telephony Performance\Successful_Data\CLK.xlsx'

dbms = xlsx

replace;

run;

but I come up with the following error:

55 proc export data=work.CLKSuccessfulSummarybyDay

2 The SAS System 08:21 Wednesday, March 23, 2016

56 outfile='R:\NAT\SMARTCENT\SMCTRCHOP\GCFTELE\Telephony Performance\Successful_Data\CLK.xlsx'

57 dbms = xlsx

58 replace;

62 run;

ERROR: Temporary file for XLSX file can not be created ->

/sas/config/dev/comp/Lev3/SASApp-CLK/R:\NAT\SMARTCENT\SMCTRCHOP\GCFTELE\Telephony Performance\Successful_Data\/CLK.$$1. Make sure

the path name is correct and that you have write permission.

I have attached the full code. Any help appreciated.

Windows 10

SAS EG 7.11 64 bit

Thanks in advance

Re: How to export proc summary to existing excel spreadsheet

Reeza — Wed, 23 Mar 2016 00:38:41 GMT

You have a couple of things going on.

The first is common, you have the output path specified as your local/network drive but your work is actually happening on a server. You need to have the file accessible to the server, either via a drive mapped from the server to your network drive. Or you need to move the Excel file to the server to append in the results and then retrieve it from there. It's probably easier to

If you have SAS Add In for Microsoft Office you can use that to extract the data to the workbook you want.

The second is you want to modify an existing Excel workbook - at one point XLSX engine did not support this. It does now, but it's definitely in the latest versions, so you need to provide what version of SAS you're on.

It looks like the latest version does allow this:

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/