topic Re: Unable to create multiple sheets in excel when doing a proc export in SAS Programming

Unable to create multiple sheets in excel when doing a proc export

ShiroAmada — Wed, 19 Sep 2018 09:42:18 GMT

proc export data = auto1(where=( PD_DATE IS MISSING ) ) outfile= "Z:\M_Auto1.xlsx" dbms= xlsxreplace; sheet="MISSING"; run; 

proc export data = auto1(where=( PD_DATE IS NOT MISSING ) )outfile= Z:\M_Auto1.xlsx" dbms= xlsxreplace; sheet="WITH_DATE"; run;

I have the code above. No errors. However, the file gets overwritten and only the tab present is the one stated in the last submitted proc export step.

I can't figure out what is missing or what is wrong with my proc export step.

Thanks in advance for your help.

Re: Unable to create multiple sheets in excel when doing a proc export

RW9 — Wed, 19 Sep 2018 09:57:07 GMT

There is a clue, hidden in your dbms statement which tells you where the replace is happening:

dbms= xlsxreplace

I believe you are missing a space there (and you are missing a quote in the second pathname), and that should only appear on the first step, so:

proc export data=auto1 (where=(pd_date is missing)) 
            outfile="Z:\M_Auto1.xlsx" 
            dbms=xlsx 
            replace; 
   sheet="MISSING"; 
run; 

proc export data=auto1 (where=(pd_date is not missing))
            outfile="Z:\M_Auto1.xlsx" 
            dbms=xlsx; 
   sheet="WITH_DATE"; 
run;

Re: Unable to create multiple sheets in excel when doing a proc export

Ksharp — Wed, 19 Sep 2018 12:44:46 GMT

An workaround way is :

libname x xlsx "Z:\M_Auto1.xlsx";
data x.MISSING;
 set auto1(where=( PD_DATE IS MISSING ) );
run;
data x.WITH_DATE;
 set auto1(where=( PD_DATE IS NOT MISSING ) );
run;

Re: Unable to create multiple sheets in excel when doing a proc export

RW9 — Wed, 19 Sep 2018 12:49:48 GMT

Dont forget to:

libname x clear;

Otherwise file will not be written/closed.

Re: Unable to create multiple sheets in excel when doing a proc export

Ksharp — Wed, 19 Sep 2018 12:55:29 GMT

An workaround way is :

libname x xlsx "Z:\M_Auto1.xlsx";
data x.MISSING;
 set auto1(where=( PD_DATE IS MISSING ) );
run;
data x.WITH_DATE;
 set auto1(where=( PD_DATE IS NOT MISSING ) );
run;

Re: Unable to create multiple sheets in excel when doing a proc export

ShiroAmada — Mon, 24 Sep 2018 05:13:40 GMT

Hi Ksharp, I got an error that says

ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

Re: Unable to create multiple sheets in excel when doing a proc export

ShiroAmada — Mon, 24 Sep 2018 05:24:34 GMT

Hi RW9

Here's the contents of the log

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='';
5          %LET _CLIENTPROJECTNAME='';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE
12       ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13         
14         GOPTIONS ACCESSIBLE;
15         
16         
17         
18         
19         proc export data=sashelp.class (where=( sex='M'))
20                     outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx"
21                     dbms=xlsx
22                     ;
23            sheet="MALE";
24         run;

NOTE: The export data set has 10 observations and 5 variables.
NOTE: "/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

25         
26         
27         proc export data=sashelp.class (where=( sex='F'))
28                     outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx"
29                     dbms=xlsx replace;
30            sheet="FEMALE";
31         run;

NOTE: The export data set has 9 observations and 5 variables.
NOTE: "/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Same thing happened. The old tab "MALE" disappeared and was replaced by FEMALE.

I tried this approach too

proc export data=sashelp.class (where=( sex='M')) 
            outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx" 
            dbms=xlsx replace  
            ; 
   sheet="MALE"; 
run; 


proc export data=sashelp.class (where=( sex='F')) 
            outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx" 
            dbms=xlsx replace; 
   sheet="FEMALE"; 
run;

Submit one the proc export step to create the "MALE" tab. Open Excel to confirm and it was created. However, when I submitted the 2nd proc export step, opened the excel file I can only see the "FEMALE" tab.

What goal is to create 2 tabs "MALE" and "FEMALE" in just 1 xlsx file.

Re: Unable to create multiple sheets in excel when doing a proc export

andreas_lds — Mon, 24 Sep 2018 05:59:01 GMT

@ShiroAmada wrote:

Hi Ksharp, I got an error that says
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

Seems you have an outdated sas-version. Please post some details about SAS-version and licenced modules, proc setinit print the relevant data to the log, remove Site-Number and other personal details before posting.

Re: Unable to create multiple sheets in excel when doing a proc export

RW9 — Mon, 24 Sep 2018 07:33:15 GMT

Please look at my previous post.

The keyword replace should only appear on the first instance of the export. If you put it on any subsequent proc exports the file is overwritten rather than being added to.

Re: Unable to create multiple sheets in excel when doing a proc export

ShiroAmada — Mon, 24 Sep 2018 09:44:41 GMT

Hi andreas_lds please refer to the contents of the proc setinit

Original site validation data
Site name:    'xxxxxxx'.
Site number:  1234567890.
Expiration:   30DEC2018.
Grace Period:  30 days (ending 29JAN2019).
Warning Period: 30 days (ending 28FEB2019).
System birthday:   20APR2018.
Operating System:   SUN 64  .
Product expiration dates:
---Base SAS Software                                                                                    30DEC2018  
---SAS/STAT                                                                                             30DEC2018  
---SAS/GRAPH                                                                                            30DEC2018  
---SAS/SHARE                                                                                            30DEC2018  
---SAS/CONNECT                                                                                          30DEC2018  
---SAS/SHARE*NET                                                                                        30DEC2018  
---SAS Enterprise Miner                                                                                 30DEC2018  
---SAS Integration Technologies                                                                         30DEC2018  
---SAS Enterprise Miner Server                                                                          30DEC2018  
---SAS Enterprise Miner Client                                                                          30DEC2018  
---SAS Data Quality Server                                                                              30DEC2018  
---SAS Enterprise Guide                                                                                 30DEC2018  
---SAS/ACCESS Interface to DB2                                                                          30DEC2018  
---SAS/ACCESS Interface to PC Files                                                                     30DEC2018  
---SAS Data Quality Server - English (United States)                                                    30DEC2018  
---DataFlux QKB English                                                                                 30DEC2018  
---DataFlux QKB Latin-1 encod                                                                           30DEC2018  
---SAS Workspace Server for Local Access                                                                30DEC2018  
---DataFlux Trans DB Driver                                                                             30DEC2018  
---SAS Framework Data Server                                                                            30DEC2018  
---SAS Add-in for Microsoft Excel                                                                       30DEC2018  
2                                                          The SAS System                           17:41 Monday, September 24, 2018

---SAS Add-in for Microsoft Outlook                                                                     30DEC2018  
---SAS Add-in for Microsoft PowerPoint                                                                  30DEC2018  
---SAS Add-in for Microsoft Word                                                                        30DEC2018

Re: Unable to create multiple sheets in excel when doing a proc export

ShiroAmada — Mon, 24 Sep 2018 09:52:52 GMT

Dear RW9,

I tried but Export cancelled on the second proc export step. No error.

18         proc export data=sashelp.class (where=( sex='M'))
19                     outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx"
20                     dbms=xlsx replace
21                     ;
22            sheet="MALE";
23         run;

NOTE: The export data set has 10 observations and 5 variables.
NOTE: "/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      

24         
25         
26         proc export data=sashelp.class (where=( sex='F'))
27                     outfile="/sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.xlsx"
28                     dbms=xlsx ;
29            sheet="FEMALE";
30         run;

NOTE: Export cancelled.  Output file /sasproj/prod/user/teamD/97_Team_Folders/08_JSB/CLASS.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: Unable to create multiple sheets in excel when doing a proc export

RW9 — Mon, 24 Sep 2018 10:31:49 GMT

This code worked fine on my machine:

proc export data=sashelp.class (where=( sex='M'))
                     outfile=".../CLASS.xlsx"
                     dbms=xlsx ;
            sheet="MALE";
         run;

proc export data=sashelp.class (where=( sex='F'))
                     outfile=".../CLASS.xlsx"
                     dbms=xlsx ;
            sheet="FEMALE";
         run;

Created an Excel file with two worksheets. I am afraid I suspect it maybe your SAS version being too old. You may need to use ods tagsets.excelxp or one of those to create your file.

Re: Unable to create multiple sheets in excel when doing a proc export

Ksharp — Mon, 24 Sep 2018 10:35:29 GMT

You could try other ENGINE.

libname x xls 'c:\......';

libname x excel 'c:\.......';

.................

libname x clear;