PascalMaurice:
I'm at work now. Here (below) is a cut and paste from a functioning program. This may be helpful in terms of the syntax which can be a bit tricky. The syntax I included above isn't quite right. Better to use this actual working code as an example. This code reads through a list of tabs (worksheets) exported as text from two different Excel workbooks, importing each tab in turn (163 of them for each Excel file) into SAS. It compares each of the two sets tabs, a 100% sample vs. a 1% sample of data, looking for significant differences in the distribution of values and writes a report.
DATA _NULL_; SET WORK.Tab_Names; CALL EXECUTE ('%NRSTR(%Process_Excel_Tab(Tab_Name=' || strip(TabName) || ');)'); RUN; %Check_SYSERR;
Inside the macro, I set up a number of parameters based on the name of the Tab that is passed into the macro. These parameters then control a variety of programs that process data based on the parameters.
%MACRO Process_Excel_Tab(Tab_Name=);
%LET DeBug = *;
%LET Data_Type = 100;
%LET FileIn = &DataLib&Tab_Name._&Data_Type;
%LET Data_Format = txt;
%LET Report_Title = &Tab_Name._&Data_Type;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
TITLE "&Report_Title";
PROC CONTENTS DATA=Out.Percent_&Data_Type POSITION;
RUN &Control_Value;
%Check_SYSERR;
PROC PRINT DATA=Out.Percent_&Data_Type;
RUN &Control_Value;
%Check_SYSERR;
/*--------------------------------------------------------------------------*/
%LET DeBug = *;
%LET Data_Type = 1;
%LET FileIn = &DataLib&Tab_Name._&Data_Type;
%LET Data_Format = txt;
%LET Report_Title = &Tab_Name._&Data_Type;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
TITLE "&Report_Title";
PROC CONTENTS DATA=Out.Percent_&Data_Type POSITION;
RUN &Control_Value;
%Check_SYSERR;
PROC PRINT DATA=Out.Percent_&Data_Type;
RUN &Control_Value;
%Check_SYSERR;
/*--------------------------------------------------------------------------*/
%LET DeBug = *;
%LET Data_Type1 = 1;
%LET Data_Type100 = 100;
%LET HTMLout = &HTMLlib&Tab_Name._&Data_Type1._Percent_vs_&Data_Type100..html;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Compare_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
/*--------------------------------------------------------------------------*/
TITLE "&Tab_Name. -- &Data_Type1. Percent vs. &Data_Type100. Percent";
PROC CONTENTS DATA=Out.Percent_&Data_Type100._Final POSITION;
RUN &Control_Value;
%Check_SYSERR;
ODS HTML BODY="&HTMLout";
PROC PRINT DATA=Out.Percent_&Data_Type100._Final;
ID Range_Start Range_End ;
RUN &Control_Value;
%Check_SYSERR;
ODS HTML CLOSE;
PROC DELETE DATA=Out.Percent_&Data_Type1;
RUN &Control_Value;
%Check_SYSERR;
PROC DELETE DATA=Out.Percent_&Data_Type100;
RUN &Control_Value;
%Check_SYSERR;
PROC DELETE DATA=Out.Percent_&Data_Type100._Final;
RUN &Control_Value;
%Check_SYSERR;
%MEND Process_Excel_Tab;
I wrote this code in a hurry in one night, so it's not the most elegant, but perhaps this may be helpful as something of an example how to work with macro variables while processing tabs exported as text from Excel. I apologize if you already know all of this.
Jim
... View more