topic Get Records That meet One Value from Other Column in SAS Programming

Get Records That meet One Value from Other Column

jei — Mon, 06 Aug 2018 04:58:46 GMT

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!

Re: Get Records That meet One Value from Other Column

PeterClemmensen — Mon, 06 Aug 2018 05:22:28 GMT

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?

Re: Get Records That meet One Value from Other Column

hashman — Mon, 06 Aug 2018 07:07:13 GMT

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

Re: Get Records That meet One Value from Other Column

PeterClemmensen — Mon, 06 Aug 2018 13:38:18 GMT

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;