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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.