Export mulitple data sets, to one workbook, multiple worksheets Excel

[deleted_user]

Good morning,

I need a little help, our organization has recently moved from SAS 9.1/9.2 to SAS EG 4.3, and I am struggling with moving some of my existing code/functions over to EG.

one the current roadblocks I am having is the following

I have a program that is creating multiple datasets, for the purpose of this example, lets call them:

work.dataset1
work.dataset2
work.dataset3
[...]


in my old sas code, I had a simple proc export:

PROC EXPORT DATA= WORK.work.dataset1
OUTFILE= "C:\sheet1.xls"
DBMS=EXCEL REPLACE;
SHEET="dataset1";
RUN;

this worked beautifully.

now in EG, I can't seem to figure out how to do the same thing... the closes I have gotten is creating the exports, but I can't control what sheet the export goes to, and it always overwrites the entire book... so instead of having a workbook with 8 sheets... I have 1 workbook with the last "export" that I set.

what's the solution for this... is this even possible in EG?

[Cynthia_sas]

Hi:
The code at the bottom of this posting worked for me in EG 4.2 in a code node. Note that the OUTFILE= name stays the same and the DATA= and SHEET= values differ for each of my datasets.

Using this code, the first dataset goes into sheet1, the second into sheet2, the third into sheet3 (each named as specified). Without seeing ALL of your code, it is hard to figure out what the issue might be. Were you by any chance using a macro program to iterate through a list of datasets???

Note that I use the DBMS= value for Excel 2002 -- this is to ensure that Excel creates an Excel97-2003 compliant XLS file -- and avoids any .XLSX issues if you do not have SAS 9.2 Phase 2.
As described here: http://support.sas.com/kb/32/455.html

cynthia
[pre]
** Proc Export method to create multiple sheets;

proc sort data=sashelp.shoes out=Pshoes;
where region = 'Pacific';
by region subsidiary;
run;

proc sort data=sashelp.shoes out=WEshoes;
where region = 'Western Europe';
by region subsidiary;
run;

proc sort data=sashelp.shoes out=Drshoes;
where product contains 'Dress';
by region subsidiary;
run;

proc export data=Pshoes
outfile= "c:\temp\exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;

proc export data=WEshoes
outfile= "c:\temp\exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;

proc export data=DRshoes
outfile= "c:\temp\exp_method.xls"
dbms=excel2002 replace;
sheet="Dressy";
run;
[/pre]

[mkm]

Hi Cynthia, Don't you think they should have the PC Files Server running at these sites as a service to utilise proc export like the way you described or do they work regardless(?)
Thanks

[deleted_user]

I can confirm... proc export does not work with my current org's setup...

does this mean i am stuck ?

[mkm]

"SAS / Access to PCFiles" makes my life lot easier from the server side not from the client side of things but otherwise try reading up on this

http://www2.sas.com/proceedings/forum2008/192-2008.pdf

[ChrisHemedinger]

I guess the question is: why doesn't PROC EXPORT work? Did you drop SAS/ACCESS to PC Files when you moved to your new environment? In general, your old program could still be run via EG, but only if all of the pieces are there.

Other disruptive factors:

- your new SAS environment is Unix-based. That means that even if you have SAS/ACCESS to PC Files, you need to set up the PC Files Server and change your code to use DBMS=EXCELCS.

- your new SAS environment is Windows, BUT Windows 64-bit with SAS for Windows x64. Because the 32-bit data providers can't be used directly from the 64-bit SAS process, you still would need to use DBMS=EXCELCS with a PC Files Server instance.

If you don't have SAS/ACCESS to PC Files, you might want to check out this new custom task that does some (but not all) of what you want:

http://blogs.sas.com/sasdummy/index.php?/archives/205-Export-to-Excel-20072010-using-SAS-Enterprise-...

Chris

[Cynthia_sas]

Hi:
It depends. It is my understanding the you may need the PC File Server if you have 64 bit Windows and you may have to also change your DBMS value to EXCELCS, per this Tech Support note:
http://support.sas.com/kb/37/479.html

Since I only have a 32 bit Windows machine running SAS and EG 4.2, the posted code worked on my installation to create a multi-sheet workbook with 3 sheets. I did not need the PC File Server to run my code. I do not currently have 64 bit SAS on a machine with PC File Server to test other scenaarios.

Here is the doc on the PC File server:
http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002645029.htm
The documentation indicates that starting with SAS/ACCESS 9.2, the server can run in two modes: Server Mode as a desktop application (available before 9.2), and Service Mode as a Windows service. Only one instance of the server can be running on a single PC at any given time.

So, my recommendation is to make sure that
1) you have an operating system configuration that requires PC File Server
2) make sure you have the PC File Server installed
3) make sure that you are using the PC File Server in one of the two modes and that the server is started appropriately
4) make sure you are using the correct code needed for the PC File Server

or, work with Tech Support on this issue. Tech Support can verify your Windows/SAS/EG versions and try to replicate the error message on a matching system. And, they can help you debug any PC File Server installation or configuration issues that you may have.

cynthia