As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Updating an Excel Table Using the Excel Libname Engine

From sasCommunity
Jump to: navigation, search

An Excel named-range (or spreadsheet) can easily be deleted by SAS and written over using the Excel LIBNAME engine. The tricky parts are that the SCAN_TEXT=NO option is needed and the target should be rewritten to before the LIBNAME connection is closed.

Create an Excel File

 %let file="Table Delete Example.xls";
 libname exbk excel &file.;
 
 data exbk.boys exbk.girls;
     set sashelp.class;
     if Sex='F' then output exbk.girls;
                else output exbk.boys;
 run;
 libname exbk clear;

Review the Results

 options noxsync noxwait;
 %sysexec &file;

Then close the workbook and run this

 %let file="Table Delete Example.xls";
 libname exbk excel &file. scan_text=no;
 
 data boys;
     set exbk.boys; output;
     set exbk.boys; output;
 run;
 
 proc datasets lib=exbk nolist;
     delete boys;
 quit;
 
 data exbk.boys;
     set boys;
 run;
 
 libname exbk clear;

Review the Results

 %sysexec &file;

Close the workbook and then run to delete

 %sysexec del &file;