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.
What constitutes a duplicate? Completely identical observations, or just the same name within a rollno group, or just rollno?
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.
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;
I'm shocked KurtBremser, using SQL when base would do :smileyconfused:
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.
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.
I need a query send email with a set of duplicate data to my email id if there is no duplicate no email.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.