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)
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.