Hi everyone,
If I have a data like this.
data t;INPUT id dis;cards;
1 0
1 1
2 0
3 1
3 0
;
There is a duplicate when ID = 1 and 3.
I want to remove them but keep the dis status is 1.
I can do to by sorting the data by ID and descending the variable of dis. Then I can use the proc sort with nodupkey statement to keep the first id in the data set.
However, I want to learn how to do it by Proc SQL.
I try use the following syntax.
proc sql; select distinct ID, dis from t HAVING dis=1; quit;
But, it turns out that the observation 2 is not included because its dis status= 0.
Further, how to do that when there are more complex relationships and more variables?
For example, if there are two rule.
data t;INPUT id dis exp;cards;
1 0 1
1 1 2
2 0 3
3 1 4
3 0 5
;
I want to pic up the distinct ID when Dis stautus = 1 and Exp status = 2,3,4.
Can anyone tell me how to do?
thx.
In your simple case your sql should like this:
proc sql;
create table want as
select distinct id, dis
from t
group by id
having dis = max(dis);
But for the more complicated examples you describe, the sql would get pretty ugly and, and I understand you're keen to explore sql, its really not the best option.
Sort and Data step is the simplest:
proc sort data=t ;
by id descending dis descending exp;
run;
data want;
set t;
by id;
if first.id;
run;
In your simple case your sql should like this:
proc sql;
create table want as
select distinct id, dis
from t
group by id
having dis = max(dis);
But for the more complicated examples you describe, the sql would get pretty ugly and, and I understand you're keen to explore sql, its really not the best option.
Sort and Data step is the simplest:
proc sort data=t ;
by id descending dis descending exp;
run;
data want;
set t;
by id;
if first.id;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.