Proc Export creating a blank SAS_empty_ sheet in the exported .xlsx file

[kannareddy0]

When exporting to .xlsx I used the following code

proc export data=MARKET outfile = "&path.\Market Report.xlsx" dbms=EXCELCS replace;

sheet= "MARKET";

run;

The export ran successfully. In the excel that SAS created, there are two sheets, SAS_empty_ and Market.

I want the Market sheet, but the SAS_empty_ sheet is annoying and I don't want that. what can I do to stop SAS from creating this blank sheet.

[SASKiwi]

This is a known feature as explained here:

http://documentation.sas.com/?docsetId=acpcref&docsetTarget=n1qk7lv0f535qkn1mydookwetyrp.htm&docsetV...

Do you get the same problem if writing to an XLS?

You could avoid the problem using a different engine, for example DBMS = EXCEL, although SAS/ACCESS to PC Files might require additional setup to allow that.

[Vince_SAS]

Can you use the XLSX engine?

proc export data=sashelp.class
  outfile='C:\temp\class.xlsx'
  dbms=xlsx
  replace;
  sheet= 'class';
run; quit;

Vince DelGobbo

SAS R&D

[ElvisK]

Hello - i encountered the same issue but I need to use PCFILES since i'm creating/transferring xlsx files from SAS EG/Unix to Windows Shared Directory Folders. Here is my code - everything works but I don't like seeing the _SAS_empty_ and i would like to do 1 of 2 options - A) somehow write a pass-through statement to drop the tab in addition to the table or B) Rename the _SAS_empty_ to something else and load data into that tab. Here is my code:

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog nostsuffix;
LIBNAME MYXLSX PCFILES
server="<server>" 
serveruser='nt_a\xxxxxxxx'   
serverpass='xxxxxxxxxxxx'     
PATH= '\\<PATH>\Defects Tracking 02.15.2020.XLSX'       /*CREATE THE NEW FILE*/
PORT=9621
;

DATA MYXLSX.DAILYIMPORT;            /*CREATE NEW TAB AND LOAD DATA*/
SET  WORK.DAILYIMPORT;
RUN;

PROC SQL;
CONNECT TO PCFILES  AS DB (SERVER="<SERVER>" PATH='\\<PATH>\Defects Tracking 02.15.2020.XLSX'
serveruser='nt_a\XXXXXXXX' serverpass='XXXXXXXXXX' PORT=9621);
EXECUTE(DROP table  `_SAS_empty_` ) BY DB;     /*ATTEMPT TO DROP WHOLE TAB BUT ONLY DROPS DATA*/
QUIT;

Any help or support would be greatly appreciated!

Thank You,

[Vince_SAS]

If I understand correctly, SAS is running on UNIX and you need to create XLSX files that reside on Windows.  You use the PC Files Server to accomplish both tasks, but don't like the "_SAS_empty_" worksheet that is created.

I don't think you can suppress or delete that worksheet; you would need to use DDE or Visual Basic code to post-process the file.

Does your IT department provide any other way to get the files from the UNIX system to the Windows system?  Maybe a UNIX location that is mapped to a Windows shared drive or some other technique?

If so, then you can use the XLSX engine instead of PCFILES.

Vince DelGobbo

SAS R&D

[ElvisK]

Unfortunately my Microsoft excel is 32BIT and SAS EG  is 64BIT

[Vince_SAS]

Can you provide more information about your concern?

There are no bitness issues with the XLSX engine.

Vince DelGobbo

SAS R&D

[ElvisK]

ok - I have tried to either import/export or assign a libname using the xlsx engine and it does not work in any of those circumstances. The only time it works is if I am creating an xlsx file from a sas data set and storing/exporting to another unix folder?

What should the code look like if it was exporting or assigning a libname using the xlsx engine? 

I pasted my code up that I currently using with pc file engine..thank you

libname:

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog nostsuffix; 
LIBNAME MYXLSX PCFILES
server="<server>" 
serveruser='nt_a\xxxxxxx' /*WINDOWS USERNAME*/
serverpass='xxxxxxxxx' /*WINDOWS PASSWORD*/
PATH= '\\<serverpath>\Defects Tracking 02.15.2020.XLSX'
PORT=9621
;

PROC IMPORT OUT=&LIBR..DAILYIMPORT
DATAFILE="\\<server>\Defects Tracking &PRIOR_MNTH..&PRIOR_DY..&PRIOR_YR..xlsx" 
DBMS=EXCELCS REPLACE;/* 
SHEET='DAILY_FILE'; 
server="<server>"; 
serveruser=&WINDWS_NME; 
serverpass=&WINDWS_PAS;
port=9621; 
RUN; 

PROC EXPORT DATA=WORK.DFCT_VAL_1 
OUTFILE='\\<server>\Defects Tracking 02.14.2020A.XLSX' REPLACE 
DBMS=EXCELCS;
SHEET="DFCT_VAL_1"; 
server="<SERVER>"; 
serveruser='#######################'; 
serverpass='########################'; 
PORT=9621;
RUN; 

[ElvisK]

Any thoughts or suggestion?

[Vince_SAS]

Try this sample code:

*  Directory on UNIX system, without quotation marks;

%let PATH=directory-specification;

*  LIBNAME export example;

libname xl xlsx "&PATH/sashelp.xlsx";

data xl.class; set sashelp.class; run; quit;

libname xl clear;

*  PROC EXPORT example;

proc export data=sashelp.retail
  file="&PATH/sashelp.xlsx"
  dbms=xlsx
  replace;
  sheet='retail';
run; quit;

*  LIBNAME import example;

libname xl xlsx "&PATH/sashelp.xlsx";

data work.retail; set xl.retail; run; quit;

libname xl clear;

*  PROC IMPORT example;

proc import out=work.class
  file="&PATH/sashelp.xlsx"
  dbms=xlsx
  replace;
  sheet='class';
run; quit;

Vince DelGobbo

SAS R&D

[ElvisK]

Thank you for your help Vince - Yes the following code works but it i'm not sure how it solves the issue since these xlsx files are residing on a unix shared directory directory and not on a windows shared directory folder?  

[Vince_SAS]

That gets back to my earlier question:

---

@Vince_SAS wrote:

Does your IT department provide any other way to get the files from the UNIX system to the Windows system? Maybe a UNIX location that is mapped to a Windows shared drive or some other technique?  

---

Contact your IT department to see if they have something set up.  Many installations have ways to share files between UNIX and Windows systems.  If not, then try FTP.

Vince DelGobbo

SAS R&D

[ElvisK]

Thank you Vince - I had a discussion with our admins and created a ticket to get our pc folder main location mapped to a unix drive. I hope someone in the same situation reads this and doesn't attempt to give it a try on their own.

[fdsaaaa]

Sounds like you are using Enterprise guide .

I can't test this on my current setup, but I think you could use copy files task to download your .xlsx from unix to windows . 

https://blogs.sas.com/content/sasdummy/2013/05/20/export-and-download-any-file-from-sas-enterprise-g...