BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

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!

9 REPLIES 9
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Garnet | Level 18

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
Lapis Lazuli | Level 10
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
Super User

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
Lapis Lazuli | Level 10

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
Super User

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
Lapis Lazuli | Level 10
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
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1115 views
  • 0 likes
  • 3 in conversation