I have 7 similar data sets, and I want to select cases that exist in two data sets, so I have 21 combinations of 2 of the 7 sets.
so I want to use a macro to speed this up. Below is my code:
%macro both(A,B);
proc sql;
create table &A&B as
select A.ID, A.Gender, A.age
from &A.demographics as A, &B.demographics as B
where A.ID = B.ID;
quit;
* then count;
proc sql noprint;
select count(*)
into :OBSCOUNT
from &A&B
quit;
%put Count=&OBSCOUNT.;
%mend both;
%both(data1,data2)
It works. But I still have to type the 21 combinations, like %both(data1,data2), %both(data1,data3), ...
How can I improve it with a do loop?
also, in the last step,
%put Count=&OBSCOUNT.;
can I print out the two data sets names, &A and &B? how?
Thank you
My fault. (The code is untested.) This is wrong:
%let n = %sysfunc(countw, &list);
Should be:
%let n = %sysfunc(countw(&list));
Look at the CALL EXECUTE documentation.
Assuming you want to call a macro to do the work for you and cut down on the possibility of human error:
%overlap (list=A B C D E F G)
Here would be a way to program it:
%macro overlap (list=);
%local i j n;
%let n = %sysfunc(countw, &list);
%do i=1 %to &n-1;
%do j=&i+1 %to &n;
%both (%scan(&list, &i), %scan(&list, &j))
%end;
%end;
%mend overlap;
You can always modify the %PUT statement inside %BOTH:
%put COUNT=&obscount A=&a B=&b;
My fault. (The code is untested.) This is wrong:
%let n = %sysfunc(countw, &list);
Should be:
%let n = %sysfunc(countw(&list));
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.