Proc sql

[SASPhile]

      select   cust_xref_id,ssn,id_nbr,count(id_nbr) as card_count from card_master
                  where cust_xref_id,ssn in (select cust_xref_id,ssn from card_master
                                   where id_nbr in (&id_nbr)
             group by cust_xref_id,ssn.id_nbr

The user passes the id_nbr for which ssn and cust_xref_id are populated.The idea is to get the count of number of cards for that combination of cust_xref_id and ssn.
and then join the above table with table on id_nbr,populating the card_count

Can a where clause like above be used?

[DBailey]

select  

     t1.cust_xref_id,

     t1.ssn,

     t1.id_nbr,

    count(t1.id_nbr) as card_count

from

     card_master t1

     inner join (select distinct cust_xref_id, ssn from card_master where id_nbr in (&id_nbr)) t2

          on t1.cust_xref_id=t2.cust_xref_id and t1.ssn=t2.ssn

group by

     t1.cust_xref_id,

     t1.ssn.id_nbr

;

[Reeza]

Could it be done with exists and corr? Just curious, I'm not sure

[SASPhile]

Using SAS, I have to connect to a database to get the results.I'm not sure if Corr works or not?

[SASPhile]

data try;
input cust_xref_id ssn id_nbr;
cards;
1234 1111 123456
1234 1111 234567
1234 1111 345678
1234 1111 456789
2345 2222 891023
2345 2222 910234
;
run;

the idea is: when the user passes the value 345678 for id_nbr,the cust_xfer_id abd ssn are 1234 and 1111.
  for this combination there are four cards.(123456,234567,345678,456789)