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

There are A, B, C libraries.

In each library x, y, z number of data sets.

In A library the data sets range from a1,a2,.....,ax

In B library the data sets range from b1,b2,b3,...,by

In C library also the data sets range from c1,c2,c3,....cz

Now we need to check each data set and need to see are there any duplicates present?

and need to write the required information into final dataset. It should contain these columns.

library datasetname totalobservations duplicatescount

1 ACCEPTED SOLUTION

Accepted Solutions
UrvishShah
Fluorite | Level 6

Hi,

How about the following one...it will be good if you post some sample data and output you want...

However,here is my try based on my understanding of your requirement...

proc sql;

   create table test as

   select libname as lib_name,memname as dsn,

          nobs as tot_obs

   from sashelp.vtable

   where libname ^in ("SASHELP","SASUSER","WORK")

   order by libname;

quit;

proc sort nodupkey data = test dupout = test1;

   by dsn;

run;

proc sql noprint;

   select distinct compress(quote(dsn)) into :duplicates_dsn separated by ","

   from test1;

   create table test1 as

   select lib_name,dsn,tot_obs,count(dsn) as tot_dup

   from test1

   group by dsn;

quit;

%put &duplicates_dsn. &tot_dup.;

data want;

  set test test1;

  by dsn;

  if tot_dup = . then tot_dup = 0;

run;

-Urvish

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

Homework?

Reeza
Super User

So what's the question?  What is considered a duplicate?

Have you tried querying the sashelp.vtable or dictionary.tables? It will give you columns 1-3, so you really only need column 4.

UrvishShah
Fluorite | Level 6

Hi,

How about the following one...it will be good if you post some sample data and output you want...

However,here is my try based on my understanding of your requirement...

proc sql;

   create table test as

   select libname as lib_name,memname as dsn,

          nobs as tot_obs

   from sashelp.vtable

   where libname ^in ("SASHELP","SASUSER","WORK")

   order by libname;

quit;

proc sort nodupkey data = test dupout = test1;

   by dsn;

run;

proc sql noprint;

   select distinct compress(quote(dsn)) into :duplicates_dsn separated by ","

   from test1;

   create table test1 as

   select lib_name,dsn,tot_obs,count(dsn) as tot_dup

   from test1

   group by dsn;

quit;

%put &duplicates_dsn. &tot_dup.;

data want;

  set test test1;

  by dsn;

  if tot_dup = . then tot_dup = 0;

run;

-Urvish

sugeshnambiar
Fluorite | Level 6

you have it right !!!   you could include filesize as well ...

sassharp
Calcite | Level 5

Helpfull Answer.

jakarman
Barite | Level 11

proc compare?

---->-- ja karman --<-----
Scott_Mitchell
Quartz | Level 8

Reeza is right, you need to specify exactly what you consider a duplicate.

Is it a duplicate observation?  Or a duplicate value in each of the variables? Even duplicate values for a single observation across multiple variables.

You said Urvish's answer was helpful, so what do you actually need?

sassharp
Calcite | Level 5

From Urvish's answer I fullfilled my requirement. Looked for duplicate value in key variable.

We can mark this one as answered. But, I dont see correct answer, helpful answer buttons in this thread.

Thanks all.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1305 views
  • 0 likes
  • 7 in conversation