BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GeorgeSAS
Lapis Lazuli | Level 10

libname mylib excel 'C:\temp\aaaaaa.xlsx';

I use the excel engine can make connection to the xlsx file, all data name end with "$";(I can't use libname xlsx,The XLSX engine cannot be found)

I can read but I can't wirte.please help.

 

Thanks

 

 

This works:

  data DATA_PWZ;
   set xx2.sheet_new;
  run;

 

 

This doesn't work:

  data mylib.sheet_new;
  set mylib.sheet_new;
  if year=2000 then freq=freq/2;
  run;

1 ACCEPTED SOLUTION

Accepted Solutions
GeorgeSAS
Lapis Lazuli | Level 10

Thank Ksharp's idiea,I need do some revise,firstly I have to save xlsx as xls.then..

 

libname XL excel 'C:\temp\test\thiswork.xls' SCAN_TEXT=NO;

 


proc sql;
update xl.'Sheet3$'n
set b=999
where a=888
;
quit;


libname _all_ clear;

View solution in original post

10 REPLIES 10
SASKiwi
PROC Star

The key thing to understand here is Excel is not a proper database which means you can't update "in-place". You can't read from an Excel sheet and then update it all in the one step.

 

The solution is to read the Excel sheet into a SAS table, make all the necessary changes, then write the SAS table back to Excel:

 

data DATA_PWZ; 
  set xx2.sheet_new;
  if year=2000 then freq=freq/2;
run;
 
data mylib.sheet_new; 
  set DATA_PWZ;
run;

 

 

PGStats
Opal | Level 21

This should work:

 

/* Connect to the workbook */
libname mylib Excel 'C:\temp\aaaaaa.xlsx';

/* Read a table */
data one;
set mylib.'myData$'n;
run;

/* Delete the table if you want to overwrite */
proc sql
drop table mylib.'myData$'n;
quit;

/* Write the new table */
data mylib.myData;
set one;
if year=2000 then freq=freq/2;
run;

/* Close the connexion */
libname mylib clear;
PG
GeorgeSAS
Lapis Lazuli | Level 10
I can't delete the table.
If I use drop, it will auto recovery the sheet ,what I can do?
PGStats
Opal | Level 21

What error message do you get?

 

You should be able to drop the table. Make sure the file is not opened by some other process when you do.

PG
GeorgeSAS
Lapis Lazuli | Level 10
no error message, just the table can't be droped
GeorgeSAS
Lapis Lazuli | Level 10
no error, just table can't be drop
PGStats
Opal | Level 21

Note, I should have mentioned that it will not remove the sheet (or named range if you use a range), it will only delete the cell contents. You should be able to overwrite the table after.

PG
GeorgeSAS
Lapis Lazuli | Level 10

Hello PG,

 

AAAA.xlsx has three sheets: sheet1, sheet2, sheet3
libname XL excel 'C:\temp\AAAA.xlsx';
After I use libname XL excel , the XL folder has three sheets: sheet1$, sheet2$, sheet3$ (There is a $ follow each sheet name )



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

data XL.Sheet3;
set sashelp.class;
w=2;
run;

libname XL clear;


The old Sheet3 will be still there, but the new one I just created will become sheet31. that is so wired.

 

Ksharp
Super User

If I was right, you need define an option .

 

libname mylib excel 'C:\temp\aaaaaa.xlsx' scan_text=no ;

 

also could try SQL.

 

proc sql;

update mylib.sheet 

 set freq=freq/2

   where year=2012 ;

quit;

GeorgeSAS
Lapis Lazuli | Level 10

Thank Ksharp's idiea,I need do some revise,firstly I have to save xlsx as xls.then..

 

libname XL excel 'C:\temp\test\thiswork.xls' SCAN_TEXT=NO;

 


proc sql;
update xl.'Sheet3$'n
set b=999
where a=888
;
quit;


libname _all_ clear;

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
  • 10 replies
  • 1790 views
  • 2 likes
  • 4 in conversation