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!
@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.
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?
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.