Hi,
I have the following data:
data have; input number sequence @@; datalines; 1 1 2 1 3 1 4 2 5 1 6 2 7 1 8 1 9 3 10 1 ; run;
What I would like to obtain are the observations for which the sequence =1 and the sequence of the number right after it is greater than 1:
number | sequence |
3 | 1 |
4 | 2 |
5 | 1 |
6 | 2 |
8 | 1 |
9 | 3 |
I was trying to solve this problem with a temporary array that serves as a rolling window:
data want;
set have;
array seq [2] _temporary_;
seq [*] = sequence;
if seq[2] > 1 and seq[1] = 1;
run;
But was getting an error for the part "seq[*] = sequence" that "Expecting an arihmetic expression"
Thank you!
One way to solve it is by using the lag function. e.g.:
data want (keep=out: rename=(out_sequence=sequence out_number=number)); set have; last_sequence=lag(sequence); last_number=lag(number); if last_sequence eq 1 and sequence gt 1 then do; out_sequence=last_sequence; out_number=last_number; output; out_sequence=sequence; out_number=number; output; end; run;
Art, CEO, AnalystFinder.com
One way to solve it is by using the lag function. e.g.:
data want (keep=out: rename=(out_sequence=sequence out_number=number)); set have; last_sequence=lag(sequence); last_number=lag(number); if last_sequence eq 1 and sequence gt 1 then do; out_sequence=last_sequence; out_number=last_number; output; out_sequence=sequence; out_number=number; output; end; run;
Art, CEO, AnalystFinder.com
And here is another way to obtain the same result .. although I think using my first suggestion would be slightly faster:
data want (drop=_:); merge have have(firstobs=2 keep=sequence rename=(sequence=_sequence)); retain _next; if sequence eq 1 and _sequence gt 1 then do; output; _next=1; end; else if _next then do; output; _next=0; end; run;
Art, CEO, AnalystFinder.com
Hi,
data want(drop=flag);
do until (last.sequence);
set have end=last;
by sequence notsorted;
retain flag;
if last.sequence and sequence=1 and not last then do;
output;
flag=1;
end;
else if first.sequence and flag then do;
output;
flag=0;
end;
end;
run;
Regards,
Naveen Srinivasan
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.