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 |
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;
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;
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;
Hi - thanks for both the suggested solutions, they worked. Much appreciated 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.