revoming duplicate using proc sql when there is a certain rule.

ffgsdf — Sun, 29 Nov 2015 21:55:13 GMT

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.

Re: revoming duplicate using proc sql when there is a certain rule.

JerryLeBreton — Sun, 29 Nov 2015 23:03:33 GMT

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;

topic revoming duplicate using proc sql when there is a certain rule. in SAS Data Management

revoming duplicate using proc sql when there is a certain rule.

Re: revoming duplicate using proc sql when there is a certain rule.