Using Macro to do loop and to append all the result

[qqian]

I have a table called 'topcode' which includes 2 codes in a column called 'code'. (will be more in reality, can reach 30)

I write a query to get the 2 code values (assume code62 and code400).

proc sql noprint;
select distinct code into : top2code separated by ' ' from topcode;
quit;

I have 2 other tables having the same structure as follows:

table 1

users	code1	code5	code62	code44	code400	code999
user1	1	1	1	1	1	1
user2	1	0	1	0	1	0
user3	0	0	0	0	1	1
user4	0	0	0	1	1	0
user5	1	0	1	0	1	1

table 2

users	code2	code5	code30	code62	code400	code500
user1	1	1	0	0	0	0
user2	0	0	1	0	1	0
user3	0	0	1	0	0	1
user4	0	0	0	1	0	0
user5	0	0	1	0	0	1

I want to loop over the topcode, and get the sum of 1s in each table and append them together.

%let i = 1;
%do i=1 %to %sysfunc(countw(&top2code));

proc sql;
select sum(%scan(&top2code, &i)) as flag1, count(*)-sum(%scan(&top2code, &i)) as flag0
from table1

UNION ALL
select sum(%scan(&top2code, &i)) as flag1, count(*)-sum(%scan(&top2code, &i)) as flag0
from table2
;
quit;

%end;

However, I can only get result for the first code, which code62.

flag1	flag0
3	2
1	4

Is it possible to append code400 result below? such as:

flag1	flag0
3	2
1	4
5	0
1	4

If not append, is there a way I can get result for all codes? Since there are more codes in reality, I have to use loop instead of calculate one by one manually.

Thanks,

[Reeza · Accepted solution]

Why SQL and macros? Why not just use PROC MEANS and pass the variable list into the VAR statement? How do table1/table2 interact with each other? That's not clear.

Then your output will be in a single table anyways. Or you're combining just two which doesn't require a macro anyways.

[qqian]

Thank you! This is helpful. And I can use the following code first to pull all the codes into top2code, then directly use it in the proc means var part.
proc sql noprint;
select distinct code into : top2code separated by ' ' from topcode;
quit;
The only problem is I can set N, SUM to get the total number and total 1s, and have to create N-SUM one more step to get the count of 0s.
But this is helpful! Thanks!!

[Reeza]

You could look at PROC FREQ then since you're only interested in counts really.

[ChrisNZ]

Like this?

%macro loop;

  %local i;

  proc sql;

  create table WANT as

  %do i=1 %to %sysfunc(countw(&top2code));

    %if &i>1 then UNION ALL;

    select sum(%scan(&top2code, &i)) as FLAG1, count(*)-sum(%scan(&top2code, &i)) as FLAG0
    from TABLE1

    UNION ALL

    select sum(%scan(&top2code, &i)) as FLAG1, count(*)-sum(%scan(&top2code, &i)) as FLAG0
    from TABLE2

  %end;

  ;
  quit;

%mend;

Regarding the missing rows, what's in the log?

[ChrisNZ]

Otherwise, as Reeza suggested, something like this would arguably be more legible, and faster:

proc sql noprint;
  select distinct cats('CODE',CODE) into :top2code separated by ' ' from TOPCODE;
quit;
data ALL/view=ALL;
  length SRC $4;
  set TABLE1(keep=&top2code in=A)
      TABLE2(keep=&top2code);
  src=ifc(A,'Tab1','Tab2');
run;
proc summary data=ALL nway;
  class SRC;
  var CODE: COUNT:;
  output out=SUM(keep=SRC CODE: _FREQ_ ) sum=;
run;

You can then, for example, use proc transpose and calculate the differences in a final data step.

[qqian]

Thank you Chris! It's also helpful.