CODEWITHSUNDEEP
countsassummary
shecode
shecode

Reputation: 1726

SAS how to get summary counts within the same dataset

I have a dataset that looks like:

id,colour
12,blue
12,green
12,yellow
13,blue
14,black
15,blue
15,green

In the same data set I would like to have the counts of each id Ultimately what I want to do is eliminate the ids that have more than one

In SQL I would use a SUM OVER() windowing function, or self join the table with the counts of each id

Whats the best way to do this in SAS?

id,colour,num
12,blue,3
12,green,3
12,yellow,3
13,blue,1
14,black,1
15,blue,2
15,green,2

My end result is going to look like this in the end after excluding the duplicate ids:

id,colour
13,blue
14,black

Upvotes: 2

Views: 274

Answers (1)

Reeza
Reeza

Reputation: 21294

Use PROC SORT in SAS 9.3+ to get a set of unique observations by key variables.

proc sort data=have out=duprec nouniquekey uniqueout=want;
by id;
run;

In SAS SQL you could remerge directly - something that isn't supported in other versions of SQL. You could further limit the query with a HAVING clause to get your final output directly.

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

Upvotes: 6

Related Questions