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
Revision as of 14:54, 16 October 2017 by Paulkaefer (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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;