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.


Difference between revisions of "Updating an Excel Table Using the Excel Libname Engine"

From sasCommunity
Jump to: navigation, search
(gardening)
m (gardening)
 
Line 1: Line 1:
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.
+
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 <big><source lang="sas" enclose="none">SCAN_TEXT=NO</source></big> option is needed and the target should be rewritten to before the LIBNAME connection is closed.
  
 
== Create an Excel File ==
 
== Create an Excel File ==
Line 40: Line 40:
 
  libname exbk clear;
 
  libname exbk clear;
 
</source>
 
</source>
 +
 
=== Review the Results ===
 
=== Review the Results ===
 
<source lang="sas">
 
<source lang="sas">
 
  %sysexec &file;  
 
  %sysexec &file;  
 
</source>
 
</source>
 +
 
== Close the workbook and then run to delete ==
 
== Close the workbook and then run to delete ==
 
<source lang="sas">
 
<source lang="sas">

Latest revision as of 14:54, 16 October 2017

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;