DATA Step, Macro, Functions and more

Proc Export - To specific Sheet

Reply
Contributor JS
Contributor
Posts: 38

Proc Export - To specific Sheet

[ Edited ]

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";

 

 

 

Super User
Posts: 17,963

Re: Proc Export - To specific Sheet

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

Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

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

Super User
Posts: 17,963

Re: Proc Export - To specific Sheet

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.

Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

[ Edited ]

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

Super User
Posts: 17,963

Re: Proc Export - To specific Sheet

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

 

 

 

 

Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

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

Super User
Posts: 9,691

Re: Proc Export - To specific Sheet

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;
Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

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

 

Super User
Super User
Posts: 7,430

Re: Proc Export - To specific Sheet

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.

 

 

Valued Guide
Posts: 505

Re: Proc Export - To specific Sheet

%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");
');

Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

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.

Contributor JS
Contributor
Posts: 38

Re: Proc Export - To specific Sheet

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

Ask a Question
Discussion stats
  • 12 replies
  • 907 views
  • 6 likes
  • 5 in conversation