SAS SQL - how do I remove rows in a table where a column doesn't contain a specific variable?

Question

Unique ID	Type	TypeID
10001	Company	123422
10002	Company	123477
10002	Subsidiary	12000
10005	Company	123455
10005	Subsidiary	12125
10006	Subsidiary	12300

So I have a table where a unique ID can have either or one or two rows of data associated with it. I want to extract the rows and delete those unique IDs where it only has one row of information. For example, the top one and the bottom one from my data set above I don't need it. How can I do this in a PROC SQL step? Many thanks!

I haven't tried anything yet.

Answer 1

I cannot think why you would use PROC SQL for something like that. Just use a data step.

data want;
   set have;
   by id ;
   if first.id and last.id then delete;
run;

If you really had to do it with SQL you could use a HAVING clause.

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