topic Re: need help to write query on email send with base sas in SAS Programming

need help to write query on email send with base sas

PriyaSaha — Fri, 28 Aug 2015 10:09:52 GMT

I have class dataset as below

Rollno name marks grade

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.

Re: need help to write query on email send with base sas

Kurt_Bremser — Fri, 28 Aug 2015 10:21:23 GMT

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

Re: need help to write query on email send with base sas

PriyaSaha — Fri, 28 Aug 2015 11:37:17 GMT

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.

Re: need help to write query on email send with base sas

Kurt_Bremser — Fri, 28 Aug 2015 12:00:16 GMT

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;

Re: need help to write query on email send with base sas

RW9 — Fri, 28 Aug 2015 12:06:27 GMT

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.

Re: need help to write query on email send with base sas

RW9 — Fri, 28 Aug 2015 12:07:31 GMT

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

Re: need help to write query on email send with base sas

Kurt_Bremser — Fri, 28 Aug 2015 12:20:49 GMT

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.

Re: need help to write query on email send with base sas

hbi — Fri, 28 Aug 2015 23:15:26 GMT

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.

Re: need help to write query on email send with base sas

PriyaSaha — Sat, 29 Aug 2015 09:14:41 GMT

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