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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.