DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 205
Accepted Solution

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

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;


Accepted Solutions
Solution
‎05-01-2017 11:53 AM
Regular Contributor
Posts: 205

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

[ Edited ]

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


All Replies
Super User
Posts: 3,106

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

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;

 

 

Respected Advisor
Posts: 4,646

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

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
Regular Contributor
Posts: 205

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

I can't delete the table.
If I use drop, it will auto recovery the sheet ,what I can do?
Respected Advisor
Posts: 4,646

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

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
Regular Contributor
Posts: 205

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

no error message, just the table can't be droped
Regular Contributor
Posts: 205

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

no error, just table can't be drop
Respected Advisor
Posts: 4,646

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

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
Regular Contributor
Posts: 205

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

[ Edited ]

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.

 

Super User
Posts: 9,681

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

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;

Solution
‎05-01-2017 11:53 AM
Regular Contributor
Posts: 205

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

[ Edited ]

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 203 views
  • 2 likes
  • 4 in conversation