DATA Step, Macro, Functions and more

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

Reply
Regular Contributor
Posts: 201

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

[ Edited ]

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!

Super User
Posts: 17,750

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

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. 

Regular Contributor
Posts: 201

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

[ Edited ]

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

Trusted Advisor
Posts: 1,363

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

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.

Regular Contributor
Posts: 201

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

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.
Super User
Posts: 17,750

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

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.
Regular Contributor
Posts: 201

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

[ Edited ]

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!

Super User
Posts: 17,750

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

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. 

Regular Contributor
Posts: 201

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

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;
Super User
Posts: 17,750

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

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. 

Ask a Question
Discussion stats
  • 9 replies
  • 247 views
  • 0 likes
  • 3 in conversation