08-28-2015 06:09 AM
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.
08-28-2015 06:21 AM
What constitutes a duplicate? Completely identical observations, or just the same name within a rollno group, or just rollno?
08-28-2015 07:37 AM
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.
08-28-2015 08:00 AM
To get those records, do this:
select * from class where rollno in (select rollno from have group by rollno having count(*) > 1);
08-28-2015 08:20 AM
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.
08-28-2015 07:15 PM
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.
08-28-2015 08:06 AM
proc sort data=have out=want dupout=dups nodupkey;
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.