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?
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
;
Could it be done with exists and corr? Just curious, I'm not sure
Using SAS, I have to connect to a database to get the results.I'm not sure if Corr works or not?
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.