topic Re: counting obs. that have maximum duplicate records in SAS Enterprise Guide

counting obs. that have maximum duplicate records

jimksas — Mon, 10 Mar 2014 23:14:59 GMT

Hi Friends - i have one SAS dataset with around 1/2 millions of records with some duplicate records on field SKU. I want to know which SKU has maximum duplicate record itself and which has minimum duplicate records for same field, SKU?

Can someone please tell me how can i do this?

Thanks!

Re: counting obs. that have maximum duplicate records

stataddict — Tue, 11 Mar 2014 07:01:45 GMT

proc sql;

create table max_min as

select SKU, count(SKU) as duplicates

from your_table

group by SKU

having duplicates > 1

order by duplicates desc

;

quit;

You will get only duplicate values, unique ones will not show up.

First record will be SKU with maximum duplicates.

Re: counting obs. that have maximum duplicate records

RW9 — Tue, 11 Mar 2014 09:02:53 GMT

Updated slightly, with this you can select any number of min/max values, in this instance I want bottom and top 2 records:

proc sql;

create table WORK.WANT as

select *

from (

select TYPE,

COUNT(TYPE) as COUNT_OF_TYPE

from SASHELP.CARS

group by TYPE

)

order by COUNT_OF_TYPE;

select COUNT(TYPE)

into :NUM_OBS

from WORK.WANT;

quit;

data want;

set want;

if _n_ <= 2 or _n_ > (&NUM_OBS. - 2) then output;

run;

Re: counting obs. that have maximum duplicate records

TomKari — Tue, 11 Mar 2014 18:21:11 GMT

The query in the first response is also doable in the "Query Builder" in exactly the same way, and you can then post-process it with additional queries, the SORT task, and the RANK task.

Tom

Re: counting obs. that have maximum duplicate records

jimksas — Wed, 12 Mar 2014 05:16:57 GMT

Thanks Stataddict, RW9 and Tom.

Everything works grt...!!!

I also did validate number of observation using PROC REPORT.