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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.