DATA Step, Macro, Functions and more

selecting a specific sequence of a variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

selecting a specific sequence of a variable

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!


Accepted Solutions
Solution
‎04-08-2017 08:41 PM
PROC Star
Posts: 7,362

Re: selecting a specific sequence of a variable

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

View solution in original post


All Replies
Solution
‎04-08-2017 08:41 PM
PROC Star
Posts: 7,362

Re: selecting a specific sequence of a variable

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

PROC Star
Posts: 7,362

Re: selecting a specific sequence of a variable

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

PROC Star
Posts: 168

Re: selecting a specific sequence of a variable

[ Edited ]

 

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

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 150 views
  • 2 likes
  • 3 in conversation