This tutorial explains how to remove duplicates by a column but returns all the columns.
Suppose you want to remove duplicates based on name but returns all the variables.
data readin;Solution
input ID Name $ Score;
cards;
1 David 45
1 David 74
2 Sam 45
2 Ram 54
3 Bane 87
3 Mary 92
3 Bane 87
4 Dane 23
5 Jenny 87
5 Ken 87
6 Simran 63
8 Priya 72
;
run;
Suppose you want to remove duplicates based on name but returns all the variables.
proc sql noprint;Method 2 :
create table tt (drop = row_num) as
select *, monotonic() as row_num
from readin
group by name
having row_num = min(row_num)
order by ID;
quit;
proc sql noprint;The method 2 might not be the desired output. You can also use MIN instead of MAX.
create table tt as
select name, max(ID)as ID, max(Score) as Score
from readin
group by name;
quit;
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
sir, please display the result of query
ReplyDeletesir, please display the result of query
ReplyDeleteCan you please explain the code here? how it is working?
ReplyDeleteplease explain
ReplyDeletenot clear how it works
data b ;
ReplyDeleteinput USUBJID MONEY1 MONEY2 MONEY3 ;
cards ;
10001 120 110 300
20001 120 10 300
30002 120 900 90
;
run;
I want output in new column Money4
where i will get min value of (money1 money2 money3)
i.e
Money4
110
10
90
data want ;
Deleteinput USUBJID MONEY1 MONEY2 MONEY3 ;
MONEY4=MIN(MONEY1,MONEY2,MONEY3);
cards ;
10001 120 110 300
20001 120 10 300
30002 120 900 90
;
run;