Union using Loop - Macro Variables

[camfarrell25]

I've got the following code that creates 30 datasets (6 * 5 channels). 

I've got a loop going that creates all the datasets as I wish but now I'm stuck trying to find an efficient way to combine all the

sets without having to write it all out... 

Any suggestion???

PROC SQL;
CREATE TABLE CHANNEL 
(channel char(8));
insert into channel 
values ("ALL")
values ("ChannelF")
values ("ChannelG")
values ("ChannelH")
values ("ChannelI");

PROC SQL noprint;
select channel
into :channel1 - :channel5 notrim
from channel;
quit;

%macro putit;
%do i=1 %to i=5;
%put Row&i: Channel = &&channel&i;
%end;
%mend putit;
%putit


%macro average;
%do i=1 %to 5;
/*Canada - Received */
PROC SQL;
CREATE TABLE AVGRECCAN_&&channel&i AS SELECT
REPORTING, YRQRT, "CANADA" AS REGIONID, "RECEIVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
GROUP BY REPORTING, YRQRT, GROUP;
QUIT;
/*Toronto - Received */
PROC SQL;
CREATE TABLE AVGRECTORONTO_&&channel&i AS SELECT
REPORTING, YRQRT, "TORONTO" AS REGIONID, "RECEIVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
WHERE TORONTOIND ='TORONTO'
GROUP BY REPORTING, YRQRT, GROUP;
QUIT;
/*Provincial - Received*/
PROC SQL;
CREATE TABLE AVGRECPROV_&&channel&i AS SELECT
REPORTING, YRQRT, REGION AS REGIONID, "RECEIVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
GROUP BY REPORTING, YRQRT, GROUP,REGION ;
QUIT;
/*Canada - Approved*/
PROC SQL;
CREATE TABLE AVGAPPCAN_&&channel&i AS SELECT
REPORTING, YRQRT, "CANADA" AS REGIONID, "APPROVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
WHERE STATUS_IND = "APPROVED"
GROUP BY REPORTING, YRQRT, GROUP;
QUIT;
/*Toronto - Approved*/
PROC SQL;
CREATE TABLE AVGAPPTORONTO_&&channel&i AS SELECT
REPORTING, YRQRT, "TORONTO" AS REGIONID, "APPROVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
WHERE TORONTOIND ='TORONTO' AND STATUS_IND = "APPROVED"
GROUP BY REPORTING, YRQRT, GROUP;
QUIT;
/*Provincial - Approved*/
PROC SQL;
CREATE TABLE AVGAPPPROV_&&channel&i AS SELECT
REPORTING, YRQRT, REGION AS REGIONID, "APPROVED" AS CATEGORY, GROUP,
AVG(bestscore) as AVG_SCORE
FROM &&channel&i
WHERE STATUS_IND = "APPROVED"
GROUP BY REPORTING, YRQRT, GROUP,REGION ;
QUIT;

%end;
%mend average;
%average;

[Reeza · Accepted solution]

Use a naming convention then you can use the colon wildcard to append all that start with the same prefix. 

For example this appends all tables that start with RECEIVED_. 

Data want;

set received_:;

run;

[RW9]

Why are you doing that code at all?  I can't really tell what your underlying data is, but creating 6 * 5 datasets and then combining them seems to be mad.  Why create all those datasets, and then do exactly the same process on each of them and then combine them?  Why not combine your data in a useable model, then run the code once on that one dataset, using a by group if necessary:

proc means data=allcombined;
  var avar;
  output out=want mean=mean;
run;