Can I get with writing a programme run through observations and identify instances where the preceding and following observation are the same and the index (middle) observation equals to a specific value i.e. second observation for oo1and third observation for oo7.
oo1 | 3 |
oo1 | 4 |
oo1 | 3 |
oo2 | 4 |
oo2 | 4 |
oo2 | 4 |
oo3 | 4 |
oo3 | 2 |
oo3 | 4 |
oo4 | 4 |
oo4 | 3 |
oo4 | 2 |
oo4 | 4 |
oo5 | 4 |
oo5 | 4 |
oo5 | 2 |
oo5 | 3 |
oo5 | 2 |
oo5 | 1 |
oo6 | 2 |
oo6 | 2 |
oo6 | 4 |
oo6 | 3 |
oo6 | 2 |
oo6 | 1 |
oo7 | 2 |
oo7 | 2 |
oo7 | 4 |
oo7 | 2 |
oo7 | 2 |
oo7 | 1 |
You might want to take a look at the blog post by @LeonidBatkhan called "Hopping for the Best." In it he explains how to "look ahead" beyond the current record and get values from records that have yet to be read in the main sequential process. If you use the techniques he describes, you can see all the values on all the records for, say 'oo3'.
Jim
You could try this - not tested - might have bugs. Here, I am calling your dataset 'stuff' and I'm calling your variables 'ID' (for the oo1, oo2... column) and 'value' for the 2nd column.
* make a row variable so that you can sort by ID (oo1, oo2, oo3 etc.)
without ruining the order of the integer variable (which I am calling 'value') ;
data stuff;
set stuff;
row=_N_;
run;
proc sort data=stuff; by id row; run;
proc sql noprint;
select max(nrows) into :maxrows from (select ID, count(*) as nrows from stuff group by ID);
quit;
* set to whatever value it is you are trying to identify between two matching values ;
%let SEARCHVAL=7;
data stuff;
length row 3;
set stuff (drop=row);
by id;
array T {&maxrows} _temporary_;
array f {&maxrows} _temporary_;
retain row 0;
if first.id then do;
call missing(of T[*], of f[*]);
x=0;
end;
x+1;
T[x]=value;
f[x]=0;
if last.id then do;
i=2;
do while(i<x);
i+1;
if T[i-2]=T[i] and T[i-1]=&SEARCHVAL then f[i-1]=1;
end;
do i=1 to x;
row+1;
value=T[x];
flag=f[x];
output;
end;
end;
keep row ID value flag;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.