Select rows till a particular vale

[anandas]

Hello

I have a table where I need all rows of data till it stops at a particular value. 

The original table looks like this. 

I want the table to look like this. I want the observations to stop at the first '0' value in the 'hs' column (the third column).

Thank you

[PeterClemmensen]

data have;
input id$ term hs;
datalines;
123 200802 1
123 200901 1
123 201002 0
123 201003 0
123 201101 0
;

data want;
   set have;
   if hs=0 then do;
      output;
      stop;
   end;
   output;
run;

[anandas]

Thank you. I forgot to mention I have multiple ids. In other words, I want this condition by each unique id. I just gave an example for one unique id. Can you do for this table? Thanks.

data test;
input id term $ hs $;
datalines;
123 200802 1
123 200901 1
123 201002 0
123 201003 0
123 201101 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
245 201002 0
;
run;

[PeterClemmensen]

Just to clarify. So in this case, your want data set looks like this right?

data want;
input id term $ hs $;
datalines;
123 200802 1
123 200901 1
123 201002 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
;
run;

Also, are the values of hs always only 0s and 1s?

[anandas]

Thank you. Yes, it should look like how you have written. There are only two values in hs - 0 and 1.
Thank you.

[novinosrin]

data test;
input id term $ hs ;
datalines;
123 200802 1
123 200901 1
123 201002 0
123 201003 0
123 201101 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
245 201002 0
;
run;




data want;
_flag=0;
do until(last.id);
set test;
by id;
if not _flag and hs=0 then do;_flag=1;output;continue;end;
else if not _flag then output;
end;
drop _:;
run;

[PeterClemmensen]

Ok. Do simply like this

data want;
   set test;
   if hs='1' or (hs='0' and lag(hs)='1');
run;

[novinosrin]

@PeterClemmensen Not quite mate!, I added a couple of rows(what if)

245 200401 1
245 200402 1
245 200403 1
245 201001 0
245 201002 0
245 201002 1(What if)
245 201002 0(what if)

[anandas]

Thank you draycut. What if there are values in hs column with just 0s and no 1s like below

data have;
input id term $ hs ;
datalines;
123 200802 1
123 200901 1
123 201002 0
123 201003 0
123 201011 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
245 201002 0
321 200902 0
321 201001 0
321 201102 0
;
run;

I need to keep the id with just the 0s as is. It should look like this

data want;
input id term $ hs ;
datalines;
123 200802 1
123 200901 1
123 201002 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
321 200902 0
321 201001 0
321 201102 0
;
run;

Thanks. Your other scripts work for the test run but when I run it against my full data set I get "variable hs is uninitialized", thoughts on that?
Thanks

[novinosrin · Accepted solution]

tweaked to meet you requirement

data have;
input id term $ hs ;
datalines;
123 200802 1
123 200901 1
123 201002 0
123 201003 0
123 201011 0
245 200401 1
245 200402 1
245 200403 1
245 201001 0
245 201002 0
321 200902 0
321 201001 0
321 201102 0
;
run;


data want;
_flag=0;
do until(last.id);
set have;
by id;
if not _flag and lag(hs)=1 and hs=0 then do;_flag=1;output;continue;end;
else if not _flag then output;
end;
drop _:;
run;

[anandas]

Thank you to novinosrin and draycut. Your script worked.

[Astounding]

Jumping into the fray before this gets too complicated:

data want;

set have;

by id;

if first.id then output_yn='Y';

retain output_yn;

if output_yn='Y' then output;

if hs='0' then output_yn='N';

drop output_yn;

run;

I kept HS as a character variable, since it seems that's what is in your current data.  But converting it to numeric would be OK.

[anandas]

Thank you to astounding. Your script worked too. i will keep yours too and study it as well.

Much appreciated!