/*create workbook and sheets*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002;
Data WrkBk.class ;
     set sashelp.class;
Data WrkBk.retail ;
     set sashelp.retail;
Data WrkBk.zipcode ;
     set sashelp.zipcode;
run;     
LIBNAME WrkBk clear;



/*move range by hand*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002;
Data WrkBk.retail;
     set sashelp.retail;
run;     
LIBNAME WrkBk clear;



/*delete range*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002;
proc datasets lib=WrkBk;
delete retail;
quit;
LIBNAME WrkBk clear;




/*refresh data in new location*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002;
Data WrkBk.retail ;
     set sashelp.retail;
run;     
LIBNAME WrkBk clear;




/*something outside range needs deletion*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002;
proc datasets lib=WrkBk;
delete 'retail$'n;  *deletes full sheet;
delete retail;
quit;
LIBNAME WrkBk clear;




/*delete header row*/
/*first add header range*/

LIBNAME WrkBk EXCEL 'My Workbook.xls';
proc datasets lib=WrkBk;
delete header;
quit;
LIBNAME WrkBk clear;




/*reset retail with hidden row*/

LIBNAME WrkBk EXCEL 'My Workbook.xls';
proc datasets lib=WrkBk;
delete retail;
quit;
LIBNAME WrkBk clear;

LIBNAME WrkBk EXCEL 'My Workbook.xls';
Data WrkBk.retail;
     set sashelp.retail;
run;     
LIBNAME WrkBk clear;




/*add data to report*/
/*turn off scan text - SAS doesn't scan for longest word*/
LIBNAME WrkBk EXCEL 'My Workbook.xls' SCAN_TEXT=NO;
proc append base=WrkBk.retail data=sashelp.retail force; 
run; 
LIBNAME WrkBk CLEAR;




/*build pivot and chart  - add  more data*/

LIBNAME WrkBk EXCEL 'My Workbook.xls' SCAN_TEXT=NO;
proc append base=WrkBk.retail data=sashelp.retail force; 
run; 
LIBNAME WrkBk CLEAR;




/* DBLabel exports SAS variable labels to header row*/
LIBNAME WrkBk EXCEL 'My Workbook.xls' ver=2002; 
data WrkBk.Pauls_Report(DBLABEL=yes);
     set sashelp.class;
     label weight="Weight in Lbs";
run;
libname WrkBk clear;




/* by default SAS imports column names as variable labels */
LIBNAME WrkBk EXCEL 'My Workbook.xls' DBSASLABEL=COMPAT;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;




/*DBSASLABEL=None imports only names, not labels*/
LIBNAME WrkBk EXCEL 'My Workbook.xls' DBSASLABEL=None ;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;



/*but validvarname=any allows non standard SAS variable names*/
options validvarname=any; *not Excel specific;

LIBNAME WrkBk EXCEL 'My Workbook.xls' DBSASLABEL=Compat;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;
options validvarname=v7;




/* mixed data */
LIBNAME WrkBk EXCEL 'My Workbook.xls' ;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;



/*try with mixed data before and after row 8*/
LIBNAME WrkBk EXCEL 'My Workbook.xls' mixed=yes;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;



/*fix registry*/
LIBNAME WrkBk EXCEL 'My Workbook.xls' mixed=yes;
data importstuff;
     set WrkBk.Pauls_Report;
run;
libname WrkBk clear;