topic Re: Please help--libname excel---SAS9.3 in SAS Programming

Please help--libname excel---SAS9.3

GeorgeSAS — Sun, 30 Apr 2017 18:47:25 GMT

Hello All,

abc.xlsx has three sheets: sheet1, sheet2, sheet3

After I use libname XL excel , the XL folder has three sheets: sheet1$, sheet2$, sheet3$ (There is a $ follow each sheet name,why? )

I want to update an exist sheet(sheet3---sheet3$) ,replace it with other sas dataset(sashelp.class):

libname XL excel 'C:\temp\abc.xlsx';

proc sql;
drop table XL.'Sheet3$'n;
quit;

data XL.Sheet3;
set sashelp.class;
run;

libname XL clear;

The old Sheet3 will be still there, but the new one I just created will become sheet31. --------------?

Please help.

Thanks!

Re: Please help--libname excel---SAS9.3

Reeza — Sun, 30 Apr 2017 18:46:29 GMT

I recall having this problem in SAS 9.3, here are some things to try:

1. You refer to it with $ in one location but not the other. Try being consistent. I believe the $ refers to the named range while the reference without the $ refers to a sheet.

libname XL excel 'C:\temp\abc.xlsx';

proc sql;
drop table XL.Sheet3;
quit;

data XL.Sheet3;
set sashelp.class;
run;

libname XL clear;

libname XL excel 'C:\temp\abc.xlsx';

proc sql;
drop table XL.'Sheet3$'n;
quit;

data XL.'Sheet3$'n;
set sashelp.class;
run;

libname XL clear;

2. Try using PCFILES instead of Excel. If you don't have PCFILES server installed you may need to add it in.

libname xl pcfiles path = 'C:\temp\sample.xlsx';

What's your specific version of SAS, for example 9.3 TS2M3?

You can find your specific version using the following:

proc setinit; run;

Also, note how I included my code using the {i} icon, it's much easier to read. Please keep your code together and use the code insertion in the edition.

Re: Please help--libname excel---SAS9.3

GeorgeSAS — Sun, 30 Apr 2017 20:26:05 GMT

Hello Reeza,
Thank you very much! but the methods all not work for me:

1.

libname XL excel 'C:\temp\abc.xlsx';

proc sql;
drop table XL.'Sheet3$'n;
quit;

data XL.'Sheet3$'n;
set sashelp.class;
run;

------------ERROR: The MS Excel table Sheet3$ has been opened for OUTPUT. This table already
exists, or there is a name conflict with an existing object. This table will
not be replaced. This engine does not support the REPLACE option.

2.

libname XL excel 'C:\temp\abc.xlsx';

proc sql;
drop table XL.Sheet3;
quit;

--------------WARNING: File XL.Sheet3.DATA does not exist.
---------------WARNING: Table XL.Sheet3 has not been dropped.

data XL.Sheet3;
set sashelp.class;
run;

----------------Sheet31 will be created, sheet3 keep not change

libname XL clear;

proc setinit; run;

----------------not show specific version of SAS

397 proc setinit; run;

NOTE: PROCEDURE SETINIT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Operating System: W32_WKS .
Product expiration dates:
---Base SAS Software
31OCT2017
---SAS/STAT
31OCT2017
---SAS/GRAPH
31OCT2017
---SAS/ETS
31OCT2017
---SAS/FSP
31OCT2017
---SAS/OR
31OCT2017
---SAS/AF
31OCT2017
---SAS/IML
31OCT2017
---SAS/QC
31OCT2017
---SAS/LAB
31OCT2017
---SAS/ASSIST
31OCT2017
---SAS/CONNECT
31OCT2017
---SAS/INSIGHT
31OCT2017
---SAS/EIS
31OCT2017
---SAS Enterprise Miner
31OCT2017
---MDDB Server common products
31OCT2017
---SAS Integration Technologies
31OCT2017
---SAS/Secure Windows
31OCT2017
---SAS Text Miner
31OCT2017
---SAS Enterprise Guide
31OCT2017
---OR OPT
31OCT2017
---OR PRS
31OCT2017
---OR IVS
31OCT2017
---OR LSO
31OCT2017
---SAS/ACCESS Interface to DB2
31OCT2017
---SAS/ACCESS Interface to Oracle
31OCT2017
---SAS/ACCESS Interface to Sybase
31OCT2017
---SAS/ACCESS Interface to PC Files
31OCT2017
---SAS/ACCESS Interface to ODBC
31OCT2017
---SAS/ACCESS Interface to OLE DB
31OCT2017
---SAS/ACCESS Interface to Teradata
31OCT2017
---SAS/ACCESS Interface to MySQL
31OCT2017
---SAS/IML Studio
31OCT2017
---SAS Workspace Server for Local Access
31OCT2017
---DataFlux Trans DB Driver
31OCT2017
---SAS Framework Data Server
31OCT2017
---Reserved for Dataflux
31OCT2017
---SAS Add-in for Microsoft Excel
31OCT2017

Re: Please help--libname excel---SAS9.3

Shmuel — Sun, 30 Apr 2017 19:45:22 GMT

As much as I know you should define:

libname xl xlsx 'C:\temp\abc.xlsx';

then you can input each sheet by:

data new;

set xl.sheet1; /* or sheet 2 etc. */

...

run;

Try it.

Re: Please help--libname excel---SAS9.3

GeorgeSAS — Sun, 30 Apr 2017 19:50:53 GMT

Thank you,but I don't have that engine
libname xl xlsx 'C:\temp\abc.xlsx';
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

Re: Please help--libname excel---SAS9.3

Reeza — Sun, 30 Apr 2017 21:31:58 GMT

You should see someting like the following:

58 proc setinit; run;

NOTE: PROCEDURE SETINIT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Original site validation data

Current version: 9.04.01M4P110916

Site name: 'UNIVERSITY EDITION 2.4 9.4M4'.

Site number: 70208402.

CPU A: Model name='' model number='' serial='+2'.

Expiration: 16JUN2017.

Grace Period: 0 days (ending 16JUN2017).

Warning Period: 45 days (ending 31JUL2017).

System birthday: 09NOV2016.

Operating System: LIN X64 .

Product expiration dates:

---Base SAS Software 16JUN2017 (CPU A)

---SAS/STAT 16JUN2017 (CPU A)

---SAS/ETS 16JUN2017 (CPU A)

---SAS/IML 16JUN2017 (CPU A)

---SAS/ACCESS Interface to PC Files 16JUN2017 (CPU A)

---SAS/IML Studio 16JUN2017 (CPU A)

---SAS Workspace Server for Local Access 16JUN2017 (CPU A)

---SAS Workspace Server for Enterprise Access 16JUN2017 (CPU A)

---High Performance Suite 16JUN2017 (CPU A)

60 %put &sysvlong;

9.04.01M4P110916

I didn't see the PCFILES version? Did that not work at all?

If you don't have the XLSX engine I suspect you're not on SAS 9.3 TS2M3 which was the last version for SAS 9.3

Your next steps are:

Talk to SAS tech support to see if there's any other options

Upgrade to SAS 9.4 - upgrades are included in your annual license.

Re: Please help--libname excel---SAS9.3

GeorgeSAS — Sun, 30 Apr 2017 21:55:02 GMT

The proc setint doesn't show me Current version.

Site number: 70064409.
Expiration: 31OCT2017.
Grace Period: 45 days (ending 15DEC2017).
Warning Period: 45 days (ending 29JAN2018).
System birthday: 27DEC2016.
Operating System: W32_WKS .
Product expiration dates:
---Base SAS Software
31OCT2017
---SAS/STAT
31OCT2017
---SAS/GRAPH
31OCT2017
---SAS/ETS
31OCT2017
---SAS/FSP

......

%put &sysvlong;
-------------9.03.01M0P060711

Upgrade to SAS 9.4 now will be no charge at this time?

Thanks!

Re: Please help--libname excel---SAS9.3

Reeza — Sun, 30 Apr 2017 23:23:19 GMT

Yeah, that's the original SAS 9.3.

As as long as you have a valid SAS license you can upgrade for free. It's included as part of the license.

TBH I've only seen this version as a pirated copy, and not even recently since SAS released SAS UE which is free for users.

Usually big companies only install software after a second or third release. This is the first 9.3 version from 2011.

Re: Please help--libname excel---SAS9.3

GeorgeSAS — Mon, 01 May 2017 15:56:55 GMT

I got an alternative but not fully solution.
save xlsx file as xls,then:
libname XL excel 'C:\temp\test\thiswork.xls' SCAN_TEXT=NO;

proc sql;
update xl.'Sheet3$'n
set bbb=111
where aaa=666
;
quit;

libname _all_ clear;

Re: Please help--libname excel---SAS9.3

Reeza — Mon, 01 May 2017 21:48:26 GMT

In my experience UPDATE can still fail, which is why I usually drop first. Another option is to work with a template so you're not dropping/updating but just inserting each time.