DATA Step, Macro, Functions and more

Proc Sql Create table for duplicates across multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Proc Sql Create table for duplicates across multiple variables

[ Edited ]

My data looks like this:

 

ID  Var  Value

1  A       1

1  A       1     

1  B       2

1  B       3

2  A       4

2  B       5

 

I would like to output the duplicate observations for ID and Var:

 

ID  Var  Value

1  A       1

1  A       1

1  B       2

1  B       3

 

I've tried this:

 

proc sql;
create table want as
select distinct id, var, count(*)
from have
group by id, var
having count(*) > 1;
quit;

 

However, this gives me all the disinct records of id and var, not just the instances where they appear twice or more. It also leaves out the Value column, which I would like to keep:  

 

ID  Var 

1  A           

1  B      

1  B      

2  A      

2  B      

 

Could someone please help me with this? Thanks.


Accepted Solutions
Solution
‎07-14-2016 12:12 PM
Super User
Posts: 19,850

Re: Proc Sql Create table for duplicates across multiple variables

Change your select statement to 

select *

from...

View solution in original post


All Replies
Solution
‎07-14-2016 12:12 PM
Super User
Posts: 19,850

Re: Proc Sql Create table for duplicates across multiple variables

Change your select statement to 

select *

from...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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