topic Re: Proc Export creating a blank SAS_empty_ sheet in the exported .xlsx file in SAS Programming

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

kannareddy0 — Thu, 02 Aug 2018 23:35:36 GMT

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.

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

SASKiwi — Fri, 03 Aug 2018 02:50:58 GMT

This is a known feature as explained here:

http://documentation.sas.com/?docsetId=acpcref&docsetTarget=n1qk7lv0f535qkn1mydookwetyrp.htm&docsetVersion=9.4&locale=en

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.

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

Vince_SAS — Sat, 04 Aug 2018 11:52:08 GMT

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

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

ElvisK — Mon, 17 Feb 2020 19:46:14 GMT

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,

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

Vince_SAS — Mon, 17 Feb 2020 20:04:45 GMT

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

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

ElvisK — Mon, 17 Feb 2020 20:16:22 GMT

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

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

Vince_SAS — Mon, 17 Feb 2020 20:20:23 GMT

Can you provide more information about your concern?

There are no bitness issues with the XLSX engine.

Vince DelGobbo

SAS R&D

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

ElvisK — Mon, 17 Feb 2020 20:39:36 GMT

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;

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

ElvisK — Mon, 17 Feb 2020 21:31:20 GMT

Any thoughts or suggestion?

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

Vince_SAS — Mon, 17 Feb 2020 23:51:22 GMT

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

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

ElvisK — Tue, 18 Feb 2020 02:39:20 GMT

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?

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

Vince_SAS — Tue, 18 Feb 2020 03:04:06 GMT

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

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

ElvisK — Mon, 24 Feb 2020 20:40:19 GMT

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.

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

fdsaaaa — Mon, 24 Feb 2020 21:10:54 GMT

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-guide/