topic Re: subset in SAS Programming

subset

SASPhile — Thu, 31 Jul 2014 14:06:12 GMT

   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.

Re: subset

stat_sas — Thu, 31 Jul 2014 14:14:01 GMT

proc sql;

select * from have

group by cust_id

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

quit;

Re: subset

SASPhile — Thu, 31 Jul 2014 14:37:28 GMT

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

Re: subset

jwillis — Mon, 04 Aug 2014 15:42:05 GMT

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'.