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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.