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 🙂
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.