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,
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.