BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ffgsdf
Fluorite | Level 6

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JerryLeBreton
Pyrite | Level 9

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;

View solution in original post

1 REPLY 1
JerryLeBreton
Pyrite | Level 9

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 1 reply
  • 1512 views
  • 0 likes
  • 2 in conversation