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)

sas-innovate-2024.png

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.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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