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;
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!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.