************************************************;
* macro multivalcellimport.sas                 *;
* purpose:  to import an excel spreadsheet     *;
* with a cell that contains multiple lines     *;
* and turns each line into a variable name     *;
* date: feb 2003                               *;
************************************************;
* first we import the data                     *;
************************************************;
options mprint;
%macro multivalcell(xlsname,rangename,varname,finalname);
%let suffix = 2;
PROC IMPORT OUT= WORK.test1 
            DATAFILE= "&xlsname" 
            DBMS=EXCEL2000 REPLACE;
     RANGE="'&rangename$'"; 
     GETNAMES=YES;
RUN;

*******************************************;
* then we compute the difference in the   *;
* number of linefeeds that are found in   *;
* each observation                        *;
* we use a compress function and then     *;
* take the difference                     *;
*******************************************;

data test2;
set test1;
&varname&suffix=compress(&varname,'0a'x);
val1=length(&varname);
val2=length(&varname&suffix);
valdif=(val1-val2);
run;

*********************************************;
* Then we get the maximum difference in the *;
* linefeed characters with proc means and   *;
* feed it into a macro loop                 *;
* we add 1 to get the first variable        *;
*********************************************;

proc means max;
var valdif;
output out=valdifmax max=valdifvar;
run;

data setup; set valdifmax;
valdifvar=valdifvar+1;
call symput('valdifvar',valdifvar);
run;

options mprint;
%macro scanit(ntimes);
data &finalname; set test2;
%do i = 1 %to &ntimes ;
&varname&i=scan(&varname,&i,'0a'x);
%end;
run;
%mend scanit;

%scanit(&valdifvar);

%mend multivalcell;

*********************************************************;
* multivalcell parameter required                       *;
* 1. the name of the xls dataset                        *;
* 2. the name of the sheet to be read                   *;
* 3. the name of the variable with multiple lines       *;
* 4. the name of the final dataset you want to create   *;
*********************************************************;

%multivalcell(J:\users\gayle\chk - sample.xls,request 2,managers,sasuser.finaldata);