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
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;
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;
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?
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;
Ok. Do simply like this
data want;
set test;
if hs='1' or (hs='0' and lag(hs)='1');
run;
@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)
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;
Thank you to novinosrin and draycut. Your script worked.
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.
Thank you to astounding. Your script worked too. i will keep yours too and study it as well.
Much appreciated!
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.