DATA Step, Macro, Functions and more

Union using Loop - Macro Variables

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Union using Loop - Macro Variables

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;

Accepted Solutions
Solution
‎12-16-2016 05:11 PM
Super User
Posts: 19,770

Re: Union using Loop - Macro Variables

Posted in reply to camfarrell25

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;

View solution in original post


All Replies
Solution
‎12-16-2016 05:11 PM
Super User
Posts: 19,770

Re: Union using Loop - Macro Variables

Posted in reply to camfarrell25

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;

Super User
Super User
Posts: 7,942

Re: Union using Loop - Macro Variables

Posted in reply to camfarrell25

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 213 views
  • 0 likes
  • 3 in conversation