topic Re: Proc sql in SAS Programming

Proc sql

SASPhile — Wed, 20 Feb 2013 21:13:34 GMT

      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?

Re: Proc sql

DBailey — Wed, 20 Feb 2013 22:00:09 GMT

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

;

Re: Proc sql

Reeza — Wed, 20 Feb 2013 22:06:12 GMT

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

Re: Proc sql

SASPhile — Wed, 20 Feb 2013 22:12:00 GMT

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)

Re: Proc sql

SASPhile — Wed, 20 Feb 2013 22:13:09 GMT

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