0

I need to perform a recursive count operation on tables but here are the challenges that I am facing with.

Lets say I have tables A, B, C, D, E, F, .... Z

Here is the code snippet of what I have,

Proc sql;
create table temp as(
select count(*) 
from a 
inner join b on a.id = b.id
inner join c on a.id = c.id
inner join d on a.id = d.id
where <condition>
);

Once this code is complete I need to run the same query with B, C, D and E and update the result in same temp table that I am trying to create. This way I have to do for the entire table list that I have.

Is there a recursive sql to do this. I don't require a separate macro to call the query each time with different tables.

Sundar Krishnan
  • 33
  • 6
  • So, just to be clear, you need to know [how many IDs are in all four tables A,B,C,D], then [how many IDs are in all four tables B,C,D,E], etc.? – Joe Mar 07 '15 at 19:00

1 Answers1

2

I would not do it quite this way. 

proc sql;
  create table temp as (
    select count(case when n(a.id,b.id,c.id,d.id)=4 then 1 else 0 end) as abcd_count,
      count(case when n(b.id,c.id,d.id,e.id)=4 then 1 else 0 end) as bcde_count
    from a outer join b on a.id=b.id
      outer join c ... etc.
;
quit;

IE, just do one join and use case when... to determine what has the counts you need. Here I use n() to identify records with all 4 ids on them.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • Thanks for your response. However, The tables that I am joining are too big and the query will run longer. Do you have any other idea? – Sundar Krishnan Mar 06 '15 at 20:42
  • If this method isn't working for you due to run time you might want to try wrapping your query in a macro loop, as SAS `proc sql` doesn't support recursion in the same way as certain other large SQL languages. I don't imagine that will be much quicker though. – SRSwift Mar 07 '15 at 12:45