topic Re: Proc Sql Create table for duplicates across multiple variables in SAS Programming

Proc Sql Create table for duplicates across multiple variables

TJ87 — Wed, 13 Jul 2016 22:50:19 GMT

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.

Re: Proc Sql Create table for duplicates across multiple variables

Reeza — Wed, 13 Jul 2016 23:35:28 GMT

Change your select statement to

select *

from...