BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

My fault.  (The code is untested.)  This is wrong:

 

%let n = %sysfunc(countw, &list);

 

Should be:

 

%let n = %sysfunc(countw(&list));

View solution in original post

6 REPLIES 6
Reeza
Super User

Look at the CALL EXECUTE documentation.

fengyuwuzu
Pyrite | Level 9
Thank you. I am not familiar with this but I will look into it.
Astounding
PROC Star

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;

fengyuwuzu
Pyrite | Level 9
Thank you. I got an error: ERROR: Expected open parenthesis after macro function name not found.

I am searching about it.
Astounding
PROC Star

My fault.  (The code is untested.)  This is wrong:

 

%let n = %sysfunc(countw, &list);

 

Should be:

 

%let n = %sysfunc(countw(&list));

fengyuwuzu
Pyrite | Level 9
Yes, it works well. Thank you.
I have learned a lot again from you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 935 views
  • 0 likes
  • 3 in conversation