DATA Step, Macro, Functions and more

proc sql within a mcaro

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

proc sql within a mcaro

[ Edited ]

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


Accepted Solutions
Solution
‎01-27-2016 03:05 PM
Super User
Posts: 5,503

Re: proc sql within a mcaro

Posted in reply to fengyuwuzu

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


All Replies
Super User
Posts: 19,789

Re: proc sql within a mcaro

Posted in reply to fengyuwuzu

Look at the CALL EXECUTE documentation.

Super Contributor
Posts: 318

Re: proc sql within a mcaro

Thank you. I am not familiar with this but I will look into it.
Super User
Posts: 5,503

Re: proc sql within a mcaro

Posted in reply to fengyuwuzu

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;

Super Contributor
Posts: 318

Re: proc sql within a mcaro

Posted in reply to Astounding
Thank you. I got an error: ERROR: Expected open parenthesis after macro function name not found.

I am searching about it.
Solution
‎01-27-2016 03:05 PM
Super User
Posts: 5,503

Re: proc sql within a mcaro

Posted in reply to fengyuwuzu

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

 

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

 

Should be:

 

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

Super Contributor
Posts: 318

Re: proc sql within a mcaro

Posted in reply to Astounding
Yes, it works well. Thank you.
I have learned a lot again from you!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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