DATA Step, Macro, Functions and more

Select rows till a particular vale

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Select rows till a particular vale

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. 

Before.JPG

 

 

 

 

 

 

 

 

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

After.JPG

 

 

 

 

 

 

Thank you


Accepted Solutions
Solution
‎01-24-2018 12:35 PM
PROC Star
Posts: 1,340

Re: Select rows till a particular vale

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;

View solution in original post


All Replies
PROC Star
Posts: 1,190

Re: Select rows till a particular vale

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;
Occasional Contributor
Posts: 14

Re: Select rows till a particular vale

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;
PROC Star
Posts: 1,190

Re: Select rows till a particular vale

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?

Occasional Contributor
Posts: 14

Re: Select rows till a particular vale

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

Re: Select rows till a particular vale

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;
PROC Star
Posts: 1,190

Re: Select rows till a particular vale

Ok. Do simply like this

 

data want;
   set test;
   if hs='1' or (hs='0' and lag(hs)='1');
run;
PROC Star
Posts: 1,340

Re: Select rows till a particular vale

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

Occasional Contributor
Posts: 14

Re: Select rows till a particular vale

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
Solution
‎01-24-2018 12:35 PM
PROC Star
Posts: 1,340

Re: Select rows till a particular vale

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;
Occasional Contributor
Posts: 14

Re: Select rows till a particular vale

Posted in reply to novinosrin

Thank you to novinosrin and draycut. Your script worked.

Super User
Posts: 6,542

Re: Select rows till a particular vale

[ Edited ]

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.

Occasional Contributor
Posts: 14

Re: Select rows till a particular vale

Posted in reply to Astounding

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

 

Much appreciated!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 163 views
  • 0 likes
  • 4 in conversation