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;
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;
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;
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;
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.
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.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.