Those source tables are actually tables that I take from a shared server (I just named it inputTable_ for convenience here). I agree that it should be renamed, but alas. The goal of the code I want to write is to: Have a final output containing all "active" people. Each person has a unique "ID" variable which is present in each table. With that, they have an indicator saying whether or not they are "active". Each source table is named inputTable_n (n is an integer from 1 to 30). For n <= 13, the input table inputTable_n has an ID column and an activeIndicator column (=0 for inactive, =1 for active). For n>= 14, the input table inputTable_n has an ID column and an active_Indicator column (=0 for inactive, =1 for active). This is why I looped through the tables, from 1 to 30 - the revised code hopefully makes it clearer on the goal of the code:
%let LIB1 = ACR; %macro tableLoop(start,end);
%do i = &start. %to &end.;
proc sql;
create table output_&i. as select
ID
,case when &i. <= 13 then activeIndicator else active_Indicator end as variable_want
from &LIB1..inputTable_&i.
where variable_want = 1
;quit; %mend;
%tableLoop(1,30);
I don't have any other merge step because this is the part I'm having trouble with - I just want the proc sql to say: OK, so we are looking at n <= 13, let's pull the activeIndicator variable - and if it's n >= 14, we look for "active_Indicator" instead. I attempt this in the "case when" statement and it's also why I have the "variable_want = 1" line. Apologies for mentioning "merging" in the title, I refer to it as pulling the variable from the inputTable. I think I can run a loop instead to rename, something like...
%macro renameVar(start,end);
%do i = &start. %to *end.;
if &i.<= 13 then do;
data WRK_inputTable_&i.;
set &LIB1..inputTable_&i.;
rename activeIndicator=active_Indicator;
run;
else if &i >= 14 then do;
data WRK_inputTable_&i.;
set &LIB1..inputTable_&i.;
run;
end;
end;
%mend;
%renameVar(1,30);
... View more
