options mprint macrogen symbolgen;
**************************************************************;
* rangeimporter9                                             *;
* date: October 18 2004                                      *;
* purpose: to import ranges of data with the import procedure*;
* and extract the variable names so they can still be used   *;
**************************************************************;

dm wpgm 'clear log' wpgm;
%macro rangeimp(xlsname,shtname,range,finalsas,tempdir1,
                mixed,scantext,usedate,scantime);


*********************************************************************;
* This section of code decodes the range and turns it into columns  *;
*********************************************************************;

data xcltemp2(keep=range1 lowervar1) xcltemp3 (keep=range1 uppervar2);
length lowervar1 $ 8 uppervar2 $ 8;
lowervar1=scan("&range",1,':');
range1=upcase(compress(lowervar1,'1234567890'));
output xcltemp2;
uppervar2=scan("&range",2,':');
range1=upcase(compress(uppervar2,'1234567890'));
output xcltemp3;
run;

data mergfinal; merge rangetbl xcltemp2 xcltemp3;
by range1;
run;

data findrow; set mergfinal;
if (lowervar1 ^=' ') or (uppervar2 ^= ' ');
 if (lowervar1 ^= ' ') then do;
   call symputx('lowcolumn',columnumber);
 end;
 else if (uppervar2 ^= ' ') then do;
   call symputx('highcolumn',columnumber);
 end;
run;

**************************************************;
* Writing the lower column and upper column here *;
**************************************************;
%put &lowcolumn;
%put &highcolumn;

*********************************************************************;
* combining the names of the sheet and range to get the right sheet *;
*********************************************************************;

%let fullrange=&shtname⦥
options source2 macrogen symbolgen mprint;
libname xcl clear;

**************************************************************************;
* first we use the libname statement with excel engine and proc contents *;
* to extract the variable names                                          *;
**************************************************************************;

libname xcl excel &xlsname;
ods trace on;
run;

ods output "Variables"=varvol1;
proc contents data=xcl."&shtname"n;
run; quit;
ods output close;

********************************************************************;
* now keep just the variables from the start column and end column *;
********************************************************************;

data varvol1b; set varvol1;
keep variable num;
if (num >= &lowcolumn) and (num <= &highcolumn);
run;

***************************************************;
* This translate table here translates any        *;
* unusual characters into names that the          *;
* SAS variable names allow.   SAS variable        *;
* names allow for _1234567890 and letters only.   *;
* So this table translates any of those           *;
* characters into new values that are acceptable  *;
* I tried to think of reasonable translations here*;
* but feel free to change this as you see fit     *;
* With the translate function the to character    *;
* comes first and then the from character         *;
* With the tranwrd function the from character    *;
* comes first                                     *;
*                                                 *;
***************************************************;

data varvol1b;
set varvol1b;
array temp (31) $ _temporary_ (' ',"'",'~','`','$','#','!','@','%','^',
'&','*','(',')','+','=','_','[',']','{','}','|','\','?','/','>','<','.',
',','£','€');
do i=1 to dim(temp);
variable=TRANSLATE(TRIM(variable),'_',temp(i));
end;
variable=TRANWRD(variable,"&","and");
run; 

proc sort; by num;
run;

************************************************;
* then we reorder then numbers to start from 1 *;
************************************************;

data varvol1b; set varvol1b;
num = _n_;
run;

******************************************;
* now we import the excel with its range *;
******************************************;

PROC IMPORT OUT= WORK.TEST
            DATAFILE= &xlsname
            DBMS=EXCEL2000 REPLACE;
     RANGE="&fullrange";
     GETNAMES=NO;
     mixed=&mixed;
     scantext=&scantext;
     usedate=&usedate;
     scantime=&scantime;
RUN;

******************************************************************;
* then we extract the variable names from the import sas dataset *;
* f1 to fxx that is                                              *;
******************************************************************;

ods output "Variables"=varvol2;
proc contents data=work.test;
run; quit;
ods output close;


data varvol2b; set varvol2;
keep variable num;
rename variable=origvar;
run;

proc sort data=varvol2b; by num;
run;

*************************************************************************;
* now we merge the old variable list and the new variable list together *;
*************************************************************************;

data varfinal; merge varvol2b varvol1b;
by num;
run;

*****************************************************************;
* now we create the rename and label statement data steps here  *;
* Note that we are removing the labels which are f1-fxx as well *;
*****************************************************************;

filename test1 "&tempdir1\renamer1.sas";
filename test2 "&tempdir1\lblname1.sas";

data _null_;
set varfinal;
file test1;
if _n_=1 then put "data &finalsas; set test;";
put 'rename ' @;
put origvar '=' variable ";" ;
run;

data _null_;
set varfinal;
file test2;
if _n_=1 then put "data &finalsas; set &finalsas;";
put 'label ' @ ;
put variable '=' "'  '" ";" ;
run;

%inc "&tempdir1\renamer1.sas";
run; quit;
%inc "&tempdir1\lblname1.sas";
run; quit;

libname xcl clear;

%mend rangeimp;

****************************************************************************;
* This first part builds the excel range of columns to compare the ranges  *;
* Do not change this section                                               *;
****************************************************************************;

data rangetbl;
input range1 $ @@;
columnumber=_n_;
datalines;
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ
BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZ
CA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ
DA DB DC DD DE DF DG DH DI DJ DK DL DM DN DO DP DQ DR DS DT DU DV DW DX DY DZ
EA EB EC ED EE EF EG EH EI EJ EK EL EM EN EO EP EQ ER ES ET EU EV EW EX EY EZ
FA FB FC FD FE FF FG FH FI FJ FK FL FM FN FO FP FQ FR FS FT FU FV FW FX FY FZ
GA GB GC GD GE GF GG GH GI GJ GK GL GM GN GO GP GQ GR GS GT GU GV GW GX GY GZ
HA HB HC HD HE HF HG HH HI HJ HK HL HM HN HO HP HQ HR HS HT HU HV HW HX HY HZ
IA IB IC ID IE IF IG IH II IJ IK IL IM IN IO IP IQ IR IS IT IU IV IW IX IY IZ
;

proc sort data=rangetbl;
by range1;
run;

%rangeimp("c:\excel files\xlstestdata.xls", /* name of xls */
           sheet1$,                 /* name of sheet - note the $ must be appended on */
           a20:g100,                  /* range of data */
                                    /* the lowest value can be a2, highest value is iv65536 */
           work.myfinal,            /* output dataset */
           c:\temp,              /* temporary directory to store rename and label */
                                    /* statements                                    */
           no,                      /* mixed data */
           yes,                     /* scan text */
           yes,                     /* use date  */
           yes);                    /* scantime  */;