BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jimksas
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
stataddict
Calcite | Level 5

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.

View solution in original post

4 REPLIES 4
stataddict
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

TomKari
Onyx | Level 15

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

jimksas
Calcite | Level 5

Thanks Stataddict, RW9 and Tom.

Everything works grt...!!!

I also did validate number of observation using PROC REPORT.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1771 views
  • 6 likes
  • 4 in conversation