Hi:
Are you still dealing with this data, as described in your previous forum posting?
http://support.sas.com/forums/thread.jspa?threadID=3929&tstart=0
If so, your code below does not reflect anything based on that data. The condition in your do loop is not using the ARRAY that you define; therefore, there does not seem to be any need for your program to have the do LOOP at all. Is CATEndDate a variable in the file AllocatedHHCatDates?? I'm not even sure that your program will work with the ARRAY statement that you show -- I suspect you would get an error message.
What's supposed to be declared on the ARRAY statement are the list of variables that you want to have treated as though they're in an array structure (or the list of variables that you want to have created). So, for example, these ARRAY statements are all acceptable:
[pre]
ARRAY myarr{4} var1 var2 var3 var4;
ARRAY myarr{4} var1-var4;
ARRAY myarr{4} fred ethel lucy ricky;
[/pre]
As you can see, the array "members" do not have to be "numbered" variables. I can take any set of variables (such as FRED, ETHEL, LUCY, RICKY) and treat them as though they were in an array structure. Inside a DO loop, then, myarr(3) could be referencing either the VAR3 variable or the LUCY variable, depending on how I had declared the ARRAY.
OK...aside from that confusion of what your program is doing. Let's look at the other question. It is not a good idea to start coding a macro solution until you understand how a complete, working SAS program would look outside of a MACRO context. So, let's look at your -other- stated requirement:
What I am attempting to do is output each of the resulting datasets to Excel with a filename and sheet name which reflects the data ...
Using PROC EXPORT (the code would be different if you were using ODS or using the Excel Libname Engine), you have 2 possibilities for doing your export:
1) Export each dataset (or subset) as a separate sheet in one workbook or
2) Export each dataset (or subset) as a single sheet in separate workbooks
Knowing which form of PROC EXPORT you were going to use (or ODS or the Libname engine) might inform how you needed to collect your information from the PROC SQL step. The code below illustrates both types of PROC EXPORT. In my example, the data is all coming from one dataset: SASHELP.SHOES, although it could just as easily have come from multiple files, by adjusting the DATA= option to use the correct DATASET name (in which case, you probably would not need the WHERE data set option).
cynthia
[pre]
** 1) Proc Export method to create multiple sheets;
** in one workbook;
proc export
data=sashelp.shoes(where=(region eq "Pacific"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;
proc export
data=sashelp.shoes(where=(region eq "Western Europe"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;
proc export
data=sashelp.shoes(where=(product contains "Dress"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Dress";
run;
** 2) Alternate method to create 3 different workbooks;
proc export
data=sashelp.shoes(where=(region eq "Pacific"))
outfile= "exp_Pacific.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;
proc export
data=sashelp.shoes(where=(region eq "Western Europe"))
outfile= "exp_West_Eur.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;
proc export
data=sashelp.shoes(where=(product contains "Dress"))
outfile= "exp_Dress.xls"
dbms=excel2002 replace;
sheet="Dress";
run;
[/pre]