topic Re: how to update xlsx file use libname---------SAS 9.3 in SAS Programming

how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Sat, 29 Apr 2017 03:30:57 GMT

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;

Re: how to update xlsx file use libname---------SAS 9.3

SASKiwi — Sat, 29 Apr 2017 03:51:27 GMT

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;

Re: how to update xlsx file use libname---------SAS 9.3

PGStats — Sat, 29 Apr 2017 03:56:59 GMT

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;

Re: how to update xlsx file use libname---------SAS 9.3

Ksharp — Sat, 29 Apr 2017 04:06:30 GMT

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;

Re: how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Sun, 30 Apr 2017 02:00:41 GMT

I can't delete the table.
If I use drop, it will auto recovery the sheet ,what I can do?

Re: how to update xlsx file use libname---------SAS 9.3

PGStats — Sun, 30 Apr 2017 02:56:38 GMT

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.

Re: how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Sun, 30 Apr 2017 03:09:08 GMT

no error message, just the table can't be droped

Re: how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Sun, 30 Apr 2017 03:09:38 GMT

no error, just table can't be drop

Re: how to update xlsx file use libname---------SAS 9.3

PGStats — Sun, 30 Apr 2017 03:26:29 GMT

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.

Re: how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Sun, 30 Apr 2017 17:37:15 GMT

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.

Re: how to update xlsx file use libname---------SAS 9.3

GeorgeSAS — Mon, 01 May 2017 15:59:41 GMT

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;