topic Re: Proc Export - To specific Sheet in SAS Programming

Proc Export - To specific Sheet

JS — Wed, 17 Aug 2016 21:02:36 GMT

I'm trying to export some summary data onto a specific sheet in an excel file.

Good news is that the export runs successfully, bad news is that it overwrites the entire workbook!

I need the information on the remainder of the workbook (some formatting, formulas, and other template stuff for powerpoint). Can anyone spot what's wrong with my query?

NOTE: The export data set has 215 observations and 25 variables.
NOTE: "\\server\Dashboard.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.12 seconds
cpu time 0.01 seconds

PROC EXPORT DATA= dash.sum_taxonomy 
			outfile= "\\server\summary\Dashboard.xlsx" 
			dbms=xlsx replace;
			sheet="RawData";

Re: Proc Export - To specific Sheet

Reeza — Wed, 17 Aug 2016 20:22:53 GMT

What version of SAS are you using? Have you tried removing the REPLACE option?

Re: Proc Export - To specific Sheet

JS — Wed, 17 Aug 2016 20:38:10 GMT

Hey Reeza,

Thanks for questions.

I am using EG 5.1, but primarily leveraging the program function.

The goal would be to overwrite an existing sheet with this newer data (I'm trying to update the dashboard).

I removed the "Replace" option, and received this error:

NOTE: Export cancelled. Output file \\Server\Dashboard.xlsx already exists. Specify REPLACE
option to overwrite it.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Proc Export - To specific Sheet

Reeza — Wed, 17 Aug 2016 20:41:43 GMT

You need your SAS version, this shows it in your log.

%put &sysver;

It sounds like you're using an older version though, so perhaps try EXCEL or EXCELCS or PCFILES as DBMS instead of XLSX. I think XLSX in those versions didn't support replacement and/or multiple sheets.

Re: Proc Export - To specific Sheet

JS — Wed, 17 Aug 2016 21:02:06 GMT

Reeza,

The put function doesn't seem to reveal the SAS Version number.

I attempted to run using the DMBS = EXCEL, and I received a class not registered error.

%put &sysver;
PROC EXPORT DATA= dash.sum_taxonomy 
			outfile= "\\server\Dashboard.xlsx" 
			dbms=EXCEL;
			sheet="RawData";

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15 %put &sysver;
9.3

16 PROC EXPORT DATA= dash.sum_taxonomy
17 outfile= "\\server\Dashboard.xlsx"
18 dbms=EXCEL;
19 sheet="RawData";
20
21
22
23
24 GOPTIONS NOACCESSIBLE;
25 %LET _CLIENTTASKLABEL=;
26 %LET _CLIENTPROJECTPATH=;
27 %LET _CLIENTPROJECTNAME=;
28 %LET _SASPROGRAMFILE=;
29
30 ;*';*";*/;quit;

1 The SAS System 15:49 Wednesday, August 17, 2016

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.05 seconds
cpu time 0.01 seconds

I attempted to run on EXCELCS and received the following error:

%put &sysver;
PROC EXPORT DATA= dash.sum_taxonomy 
			outfile= "\\server\Dashboard.xlsx" 
			dbms=EXCELCS;
			sheet="RawData";

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15 %put &sysver;
9.3

16 PROC EXPORT DATA= dash.sum_taxonomy
17 outfile= "\\Server\Dashboard.xlsx"
18 dbms=EXCELCS;
19 sheet="RawData";
20
21
22 GOPTIONS NOACCESSIBLE;
23 %LET _CLIENTTASKLABEL=;
24 %LET _CLIENTPROJECTPATH=;
25 %LET _CLIENTPROJECTNAME=;
26 %LET _SASPROGRAMFILE=;
27
28 ;*';*";*/;quit;

1 The SAS System 15:51 Wednesday, August 17, 2016

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary
(volatile) Ace DSN for process 0x6730 Thread 0x5914 DBC 0xcb89b4
Excel'.
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.27 seconds
cpu time 0.00 seconds

28 ! run;
29 ODS _ALL_ CLOSE;
30
31
32 QUIT; RUN;
33

Re: Proc Export - To specific Sheet

Reeza — Wed, 17 Aug 2016 23:58:28 GMT

Put writes to the the log. You can see under the line of code in your log, your version is SAS 9.3.

Only other option I can suggest is the following macro. Hopefully someone else has better answers.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Re: Proc Export - To specific Sheet

Ksharp — Thu, 18 Aug 2016 04:32:37 GMT

Not sure if this could work.
1) Try Libname statement + xlsx/excel engine

2) Try define a range:

PROC EXPORT DATA=sashelp.class 
			outfile= "/folders/myfolders/have.xlsx" 
			dbms=xlsx replace;
			sheet="RawData$E100:P400"; 
run;

Re: Proc Export - To specific Sheet

RW9 — Thu, 18 Aug 2016 08:19:08 GMT

Whilst this isn't particularly helpful, the problem lies not in the process but the tool used. Excel is not a reporting suite (as it is not a tool for anything else either). That is where you problem lies. There are three possiblities:

1) This being the optimal solution, use a set of tools designed to do reporting on data, for instance Spotfire, you update the data and the online reports get refreshed.

2) You simulate the above but generating your reports and publishing new files (which can look similar) to a shared area.

3) You can export your data from SAS to CSV, then in Excel using VBA you can import that data and manipulate it.

Re: Proc Export - To specific Sheet

rogerjdeangelis — Thu, 18 Aug 2016 15:29:30 GMT

%utlfkil(d:/xls/existing_sheet.xlsx); /* delete if exist */

libname xls "d:/xls/existing_sheet.xlsx";
data xls.'existing_sheet'n;
 set sashelp.class;
run;quit;
libname xls clear;

* add  worksheet without affecting other worksheets;

libname xls "d:/xls/existing_sheet.xlsx";
data xls.'added_sheet'n;
 set sashelp.cars;
run;quit;
libname xls clear;

or using R

%utl_submit_r64('
library(XLConnect);
writeWorksheetToFile(file = "d:/xls/existing_sheet.xlsx", data = c0, sheet = "CLASS");
');

Re: Proc Export - To specific Sheet

JS — Thu, 18 Aug 2016 17:45:19 GMT

I attempted to run it in a specified range, and the query ran successfully, however it ended up overwriting the entire workbook.

Re: Proc Export - To specific Sheet

JS — Tue, 23 Aug 2016 19:07:23 GMT

Does this code overwrite the existing workbook? I want to keep the other sheets, and just replace one of them.

Also, the first line in the command results in an error.

16
17 %utlfkil(d:/xls/existing_sheet.xlsx);
_
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Proc Export - To specific Sheet

JS — Wed, 24 Aug 2016 17:23:50 GMT

*forehead slap. Thanks Reeza. I'll take a look at the Wiki!

Re: Proc Export - To specific Sheet

JS — Wed, 24 Aug 2016 17:28:42 GMT

That's very prudent insight.

I agree-- excel is not a reporting suite, but when it comes to analysis, oftentimes you find that ubiquity is key. (translation: this is the format my bosses want it in).

1) Spotfire isn't free.

2) That's what I'm trying to do, but the XLSX is being overwritten

3) This might be the only option. I'll export it to a seperate xlsx, and then use a vbscript to import it into the main folder

Re: Proc Export - To specific Sheet

cristi549 — Wed, 31 Oct 2018 14:04:13 GMT

The question seems to have been asked a while ago, but I couldn't find an easy fix on the web, thought I would share something that worked for me.

I am currently using SAS 9.4 and had the problem when exporting to a particular sheet to a workbook (with the replace option, as I needed it to replace the sheet). Not the scientific fix that worked for me was saving the spreadsheet I was exporting to, i.e. the template on a different Sheet to the one I was planning to export to and replace. This prevented SAS from wiping out the rest of the workbook.

Also add

DATA _null_ ; asleep = Sleep(10) ; RUN ; before every export to the workbook, makes SAS sleep for 10 seconds.

Re: Proc Export - To specific Sheet

ChrisHemedinger — Fri, 30 Nov 2018 17:35:44 GMT

PROC EXPORT with DBMS=XLSX can add/update a sheet within an existing XLSX file. That's a relatively recent feature, added in SAS 9.4. DBMS=XLSX works on Windows and Unix/Linux, and does not require any additional Microsoft code libraries or PC Files Server.

Remember, PROC EXPORT with DBMS=XLSX require the SAS/ACCESS to PC Files license/install. If you're using SAS University Edition, this has everything that you need.

See a full example in this blog post.