Conditional Lag

[eceklic]

Hi,

I am new to SAS and would appreciate some help as I have been stuck on this problem for awhile. Say, I have two variables, one is an ID and another is a counter. If an ID has the highest counter value of '4' I want to keep only that record and delete other records with the same ID. If the ID does not have the highest counter value of '4', I want to keep all the records. I work in SAS Base 9.4.

Please help and thanks 🙂

Dataset HAVE
ID	Linkage_Qual
1	4
1	3
1	2
1	1
2	3
2	2
3	4
3	3
3	2
3	1
Dataset WANT
ID	Linkage_Qual
1	4
2	3
2	2
3	4

[novinosrin · Accepted solution]

Assuming i understand your requirement

data have;
input ID	Linkage_Qual;
cards;
1	4
1	3
1	2
1	1
2	3
2	2
3	4
3	3
3	2
3	1
; 

proc sql;
create table want as
select *
from have
group by id	
having max(	Linkage_Qual=4)*Linkage_Qual=4 or  max(	Linkage_Qual=4)=0
order by id, Linkage_Qual desc;
quit;

[PGStats]

proc sort data=have; by id descending Linkage_Qual; run;

data want;
do until(last.id);
    set have; by id;
    if first.id then topLnk = Linkage_Qual;
    if Linkage_Qual = 4 or topLnk ne 4 then output;
    end;
drop topLnk;
run;

[eceklic]

Hi - thanks for both the suggested solutions, they worked. Much appreciated 🙂