topic Re: Locate and tag current, next and previous observations by variable in SAS Programming

Locate and tag current, next and previous observations by variable

Tucky — Thu, 20 Aug 2015 05:59:47 GMT

I would like to find an easier solution to a problem I am having. I can manage this task, however it's though several steps outside SAS (SQL LIMIT). At best I don't get all the observations correctly in SAS alone.

I have used a POINT and SET however I can't get the BY variables working.

Would be good to have a data step or PROC SQL to be able to achieve this in SAS alone.

The code I have modified is below. Currently, it does not discriminate between the ID, it just follows the order of the set.

data WANT;
set HAVE nobs=nobs;

if FLAG = 'Y' then do;
current = _N_;
prev = current - 1;
next = current + 1;

if prev > 0 then do;
    set HAVE point = prev;
      POSTION = 'PREVIOUS';
      output;
   end;

set HAVE point = current;
POSTION = 'CURRENT';
output;

if next <= nobs then do;
   set HAVE point = next;
    POSTION = 'NEXT';
    output;
end;

end;

run;

I have a table with multiple ID's. Within each ID there is an observation with a flag.

OBS	ID	FLAG
1	RED
2	RED
3	RED	Y
4	RED
5	RED
6	BLUE	Y
7	BLUE
8	GREEN
9	GREEN	Y
10	PURPLE
11	PURPLE
12	PURPLE
13	PURPLE	Y
14	PURPLE
15	PURPLE

I would like to locate and tag the current, previous and next observation based on OBS order (or dates) and the ID as sorted above.

The output would look like below.

OBS	ID	FLAG	POSTION
1	RED
2	RED		PREVIOUS
3	RED	Y	CURRENT
4	RED		NEXT
5	RED
6	BLUE	Y	CURRENT
7	BLUE		NEXT
8	GREEN		PREVIOUS
9	GREEN	Y	CURRENT
10	PURPLE
11	PURPLE
12	PURPLE		PREVIOUS
13	PURPLE	Y	CURRENT
14	PURPLE		NEXT
15	PURPLE

Any help would be greatly appreciated.

Re: Locate and tag current, next and previous observations by variable

Kurt_Bremser — Thu, 20 Aug 2015 07:46:29 GMT

/* dertermine the succeeding records, using lag() function and original order */

data int1 (drop=oldflg oldid);

set have;

length POSTION $ 8;

count = _N_;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'NEXT';

if flag = 'Y' then POSTION = 'CURRENT';

run;

/* turn the dataset upside down */

proc sort data=int1;

by descending count;

run;

/* dertermine the preceding records, using the reverse order */

data int2 (drop=oldflg oldid);

set int1;

oldflg = lag(flag);

oldid = lag(id);

if oldflg = 'Y' and oldid = id then POSTION = 'PREVIOUS';

run;

/* establish original order */

proc sort data=int2 out=want (drop=count);

by count;

run;

Re: Locate and tag current, next and previous observations by variable

RW9 — Thu, 20 Aug 2015 10:18:43 GMT

As an alternative, an SQL based approach:

proc sql;

create table WANT as

select A.*,

case when A.OBS=B.OBS-1 then "Previous"

when A.OBS=B.OBS then "Current"

when A.OBS=B.OBS+1 then "Next"

else "" end as POSITION

from WORK.HAVE A

left join (select * from WORK.HAVE where FLAG="Y") B

on A.ID=B.ID

order by A.OBS;

quit;

Re: Locate and tag current, next and previous observations by variable

Tucky — Thu, 20 Aug 2015 23:06:47 GMT

Thank you Kurt and RW9, both awesome answers.

Nice work.

Re: Locate and tag current, next and previous observations by variable

Tom — Fri, 21 Aug 2015 00:00:34 GMT

You can do it just using FIRST. and LAST. flags. At least for your sample data.

data want ;

set have;

by color flag notsorted ;

if last.flag and flag=' ' and not last.color then position='PREVIOUS';

else if flag='Y' then position='CURRENT';

else if first.flag and flag=' ' and not first.color then position='NEXT';

run;

Re: Locate and tag current, next and previous observations by variable

Haikuo — Fri, 21 Aug 2015 00:19:39 GMT

Elegant! I know it goes without saying with all Tom's code, but I just couldn't help with this one! Thank you Tom !

Re: Locate and tag current, next and previous observations by variable

Ksharp — Fri, 21 Aug 2015 01:28:12 GMT

data have;
input OBS ID $ FLAG $;
cards;
1 RED .   
2 RED  .
3 RED Y 
4 RED  .
5 RED   .
6 BLUE Y 
7 BLUE  .
8 GREEN .
9 GREEN Y 
10 PURPLE   .
11 PURPLE   .
12 PURPLE  .
13 PURPLE Y .
14 PURPLE  .
15 PURPLE  .
;
run;
data want;
 merge have have(firstobs=2 keep=id flag rename=(id=_id flag=_flag)); 
 length POSTION $ 20;
 lag_flag=lag(flag);lag_id=lag(id);
 if FLAG='Y' then POSTION='CURRENT';
  else if missing(FLAG) and _FLAG='Y' and id=_id then POSTION='PREVIOUS';
   else if missing(FLAG) and lag_flag='Y' and id=lag_id then POSTION='NEXT';
drop lag_: _:;
run;

Xia Keshan

Re: Locate and tag current, next and previous observations by variable

RW9 — Fri, 21 Aug 2015 07:56:23 GMT

Yes, interesting idea Tom, would not have thought about using flag as a grouping variable with First/Last.