Get Records That meet One Value from Other Column

[jei]

Hi!

I have data below

ID      Track_No   Type

001    909             A

011    909             B

002    908             C

012    908             A

002    908             B

013    907             B

003    907             A

004    906             D

005    905             A

006    904             C

007    903             A

008    903             A

009    902             A

010    902             A

011    901             C

011    901             B

I would like to get only records from columns ID, Track_No, and Type who has Type B. That is, the output should be the same as below.

ID      Track_No   Type

001    909             A

001    909             B

002    908             C

002    908             A

002    908             B

003    907             B

003    907             A

011    901             C

011    901             B

Thank you in advance for your help!

[PeterClemmensen]

Do something like this

data have;
input ID $ Track_No Type $;
datalines;
001 909 A
011 909 B
002 908 C
012 908 A
002 908 B
013 907 B
003 907 A
004 906 D
005 905 A
006 904 C
007 903 A
008 903 A
009 902 A
010 902 A
011 901 C
011 901 B
;

proc sql;
    create table want as
        select *
        from have
        group by ID
        having sum(Type="B") > 0;
quit;

EDIT: However, I don't think your desired result is in line with your data? I do not see the second record from your desired result in your start data? 

[hashman · Accepted solution]

@jei:

You've misstated the problem a bit. Judging from your sample output, you need to select the records with those Track_No numbers whose corresponding Type="B". Thus:

data have ;                                        
  input ID $ Track_No Type $ ;                     
  cards ;                                          
001 909 A                                          
011 909 B                                          
002 908 C                                          
012 908 A                                          
002 908 B                                          
013 907 B                                          
003 907 A                                          
004 906 D                                          
005 905 A                                          
006 904 C                                          
007 903 A                                          
008 903 A                                          
009 902 A                                          
010 902 A                                          
011 901 C                                          
011 901 B                                          
run ;                                              
                                                   
data want ;                                        
  if _n_ = 1 then do ;                             
    dcl hash h (dataset:"have(where=(Type='B'))") ;
    h.defineKey ("Track_No") ;                     
    h.defineDone () ;                              
  end ;                                            
  set have ;                                       
  if h.check() = 0 ;                               
run ;                                              

Or, if you prefer SQL:

proc sql ;                                                       
  create table want as                                          
  select *                                                       
  from   have                                                    
  where  Track_No in (select Track_No from have where Type = "B")
  ;                                                              
quit ;                                                           

HTH

Paul D. 

[PeterClemmensen]

If you want it by Track_No, do this

proc sql;
    create table want as
        select *
        from have
        group by Track_No
        having sum(Type="B") > 0
        order by ID;
quit;