topic Re: Select rows till a particular vale in SAS Programming

Select rows till a particular vale

anandas — Wed, 24 Jan 2018 15:29:15 GMT

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

Re: Select rows till a particular vale

PeterClemmensen — Wed, 24 Jan 2018 15:40:20 GMT

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;

Re: Select rows till a particular vale

anandas — Wed, 24 Jan 2018 15:44:39 GMT

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;

Re: Select rows till a particular vale

PeterClemmensen — Wed, 24 Jan 2018 15:53:12 GMT

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?

Re: Select rows till a particular vale

anandas — Wed, 24 Jan 2018 15:54:54 GMT

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

Re: Select rows till a particular vale

novinosrin — Wed, 24 Jan 2018 16:20:12 GMT

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;

Re: Select rows till a particular vale

PeterClemmensen — Wed, 24 Jan 2018 16:44:18 GMT

Ok. Do simply like this

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

Re: Select rows till a particular vale

novinosrin — Wed, 24 Jan 2018 17:08:18 GMT

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

Re: Select rows till a particular vale

anandas — Wed, 24 Jan 2018 17:16:22 GMT

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

Re: Select rows till a particular vale

novinosrin — Wed, 24 Jan 2018 17:26:31 GMT

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;

Re: Select rows till a particular vale

Astounding — Wed, 24 Jan 2018 17:34:40 GMT

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.

Re: Select rows till a particular vale

anandas — Wed, 24 Jan 2018 17:38:25 GMT

Thank you to novinosrin and draycut. Your script worked.

Re: Select rows till a particular vale

anandas — Wed, 24 Jan 2018 17:38:58 GMT

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

Much appreciated!