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));
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.