DATA Step, Macro, Functions and more

subset

Reply
Super Contributor
Posts: 673

subset

   there are these columns in a table: acct_number cust_id and pin.
    there are millions of records.
   but I would like to select only those which have the following combination:

   acct_nbr    cust_id    pin
    1234        111        Abc
    2345        111        pqr


   i.e if a cust_id has different acct_nbr and differt pin.

Trusted Advisor
Posts: 1,228

Re: subset

proc sql;

select * from have

group by cust_id

having count(distinct acct_nbr)>1 and count(distinct pin)>1;

quit;

Super Contributor
Posts: 673

Re: subset

Thaks Stat:

  the above table have , I need to join with another table.

  the other table has cust_id and flag. and flag has values like M,L,P and blank

   I would like to get only those values where first record is M and second is a blank.

The output looks like:

acct_nbr    cust_id    pin     flag

    1234        111        Abc    M

    2345        111        pqr

Regular Contributor
Posts: 217

Re: subset

SASPhile,

Take the second table and find all the ID's with flag M and the ID is in Have to create table 'secondM'.  Then go back to the second table and capture all the rows that have an ID in 'secondM' creating table 'secondZ'.  All the ID's in 'secondZ' are matched to your criteria. Then join the Have and Second tables where ID's match and ID's are in 'secondZ'.

Ask a Question
Discussion stats
  • 3 replies
  • 235 views
  • 0 likes
  • 3 in conversation