BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

      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?

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

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
Super User

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

SASPhile
Quartz | Level 8

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

SASPhile
Quartz | Level 8

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)

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1412 views
  • 0 likes
  • 3 in conversation