topic Re: Using Macro to do loop and to append all the result in SAS Programming

Using Macro to do loop and to append all the result

qqian — Thu, 05 Mar 2020 00:23:00 GMT

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,

Re: Using Macro to do loop and to append all the result

Reeza — Thu, 05 Mar 2020 02:46:29 GMT

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.

Re: Using Macro to do loop and to append all the result

ChrisNZ — Thu, 05 Mar 2020 04:30:00 GMT

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?

Re: Using Macro to do loop and to append all the result

ChrisNZ — Thu, 05 Mar 2020 04:32:21 GMT

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.

Re: Using Macro to do loop and to append all the result

qqian — Thu, 05 Mar 2020 15:59:00 GMT

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!!

Re: Using Macro to do loop and to append all the result

Reeza — Thu, 05 Mar 2020 16:28:19 GMT

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

Re: Using Macro to do loop and to append all the result

qqian — Thu, 05 Mar 2020 19:43:32 GMT

Thank you Chris! It's also helpful.