title: Deleting data from an Excel spreadsheet using SAS
body: <p>I have a problem which seemed simple. Although I looked around for a solution here and on internet in general and tried a couple of things that should have solved the problem, nothing works.</p>
  
  <p>I have exported from SAS to Excel using this code:</p>
  
  <pre><code>libname xls excel '..\Test3.xlsx' scan_text = no ver=2007;
  
  proc datasets lib = xls nolist;
     delete Sweden;
  quit;
  
  data xls.Sweden;
     set WORK.falcon_cases_2;
  run;
  
  libname xls clear;
  </code></pre>
  
  <p>And then append another table using this code:</p>
  
  <pre><code>libname xls excel '..\Test3.xlsx' scan_text = no ver=2007 ;
  
  proc append base = xls.Sweden
              data = WORK.falcon_cases_NorDen_2;
  run;
  
  libname xls clear;
  </code></pre>
  
  <p>I have to do it like this as the data for the two tables comes from different SAS server data tables.</p>
  
  <p>The problem is that this part,</p>
  
  <pre><code>proc datasets lib = xls nolist;
     delete Sweden;
  quit;
  </code></pre>
  
  <p>only deletes the named range which only extends to the first data set put into the spreadsheet. So if the first table is 10 rows and the second 15, adding up to 25 rows in the spreadsheet. When delete is done, only the first 10 rows have been cleared.
  I've tried using</p>
  
  <pre><code>proc sql noprint; 
     drop table xls.'Sweden$'n;
  quit;
  </code></pre>
  
  <p>but the result is the same. Both solutions seem to be limited to only clear data within the named range.</p>
  
  <p>Any suggestions?</p>