need help to write query on email send with base sas

[PriyaSaha]

I have class dataset as below

Rollno name marks grade

A11     Ram    50    C

A11     Ram    50    C

A11      Hari    60    B

C22     Priya    70   B

C33    Ram    60   B

D44    Hari    80   A

D44    priya   90   A

I want to write a query with below condition

“If class dataset contain any duplicate with Rollno then send a email with duplicate data details otherwise do not sent any email if data is not duplicate in Class dataset .”

Please help me write the query.

Thanks in advance.

[Kurt_Bremser]

What constitutes a duplicate? Completely identical observations, or just the same name within a rollno group, or just rollno?

[PriyaSaha]

In above CLASS data set, data should not be duplicate by Rollno .If it is duplicate then send a email with duplicate dataset to email id.

[Kurt_Bremser]

To get those records, do this:

proc sql;

select * from class where rollno in (select rollno from have group by rollno having count(*) > 1);

quit;

[RW9]

I'm shocked KurtBremser, using SQL when base would do :smileyconfused:

[Kurt_Bremser]

Did that mainly as a mental experiment, to see if I could get it right. Got to firmly embed the dupout= option in the back of my mind, though. Makes for a nice solution.

[hbi]

I think your SQL solution is right on target if you want both the duplicate that would have been eliminated along with the duplicate that would have been preserved :smileycool:.

The proc sort solution picks winners and losers (based on sort position), and the first observation wins.

[PriyaSaha]

I need a query send email with a set of duplicate data to my email id if there is no duplicate no email.

[RW9]

Hi,

proc sort data=have out=want dupout=dups nodupkey;

     by rollno;

run;

This will create two datasets - want with no duplicates, and dups with the duplicates.  You can then email or do whatever you want with the dups dataset.