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

[GeorgeSAS]

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!

[Reeza]

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;

Or

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. 

[GeorgeSAS]

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;

3.

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

[Shmuel]

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.

[GeorgeSAS]

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.

[Reeza]

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)

59

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.

[GeorgeSAS]

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!

[Reeza]

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. 

[GeorgeSAS]

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;

[Reeza]

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.