dm wpgm 'clear log' wpgm;

********************************************************************;
* Program name:  Norow1.sas                                        *;
* Purposse: allow sas to read an excel sheet where the header row  *;
*           is not row 1                                           *;
* Requirements:  Access to PC file formats                         *;
*                controlling terminal to launch excel              *;
********************************************************************;
* THE ONLY PART OF THE PROGRAM THAT NEEDS MODIFICATION ARE THE     *;
* MACRO PARAMETERS AT THE BOTTOM OF THE PROGRAM                    *;
********************************************************************; 

options mprint macrogen symbolgen;

%macro norow1(xlsname,originaldir,copydir,sasdata,sheetname,headrow,
              getnames,mixed,scantext,usedate,scantime);

/* setting up the macro variables here */;

%let originalxls=&originaldir&xlsname;
%let copyxls=&copydir&xlsname;
%let rangeext=$;
%let rangename=&sheetname&rangeext;

%put &originalxls;
%put &copyxls;
%put &rangename;

/* this sets up the selection of rows above the header row  */;
/* for example if the header is on row 5 , this will select */;
/* r1:r4 for its selections to delete                       */;

data setup;
endrow=(&headrow-1);
endrow2=(compress("r"||endrow));
select=compress("r1:"||endrow2);
select=left(trim(select));
call symput('selector',(select));
run;

* First we copy the original xls file to a temporary location *;

systask command "copy &originalxls &copyxls";


%put &selector;

* then we launch excel here, this is why we need a controlling terminal *;

options noxwait noxsync;
filename sas2xl dde 'excel|system';
data _null_;
  length fid rc start stop time 8;
  fid=fopen('sas2xl','s');
  if (fid le 0) then do;
    rc=system('start excel');
    start=datetime();
    stop=start+20;
    do while (fid le 0);
      fid=fopen('sas2xl','s');
      time=datetime();
      if (time ge stop) then fid=1;
      end;
    end;
  rc=fclose(fid);
run;

* then we open the copied xls file and clip out the rows above that header *;
* the file is then saved to the same copied name.  This protects the original *;

filename cmds dde 'excel|system';

data _null_;
file cmds;
put '[open("'"&copyxls"'")]';
put '[workbook.activate("'"&sheetname"'")]';
put '[select("'"&selector"'")]';
put '[edit.delete(3)]';
put '[error(false)]';
put '[save.as("'"&copyxls"'")]';
put '[quit()]';
run;

* then we import the copied xls file with the rows that have been clipped *;

proc import datafile="&copyxls" out=&sasdata replace;
range="&rangename";
getnames=&getnames;
mixed=&mixed;
scantext=&scantext;
usedate=&usedate;
scantime=&scantime;
run;

* then we delete the copied xls file here so it no longer exists *;

systask command "del &copyxls";
run;

%mend;



%norow1(nowrow1.xls,      /* the xls file name */
        c:\testsas\,      /* directory of the xls file */
        c:\temp\,         /* where the temporary xls file goes */
        work.mydata,      /* the sas dataset name */
        sheet2,           /* the sheet name */
        5,                /* the starting header row */
        yes,              /* getnames */ 
        no,               /* mixed data */ 
        yes,              /* scan text */ 
        yes,              /* use date  */
        yes);             /* scantime  */;