DATA Step, Macro, Functions and more

need help to write query on email send with base sas

Reply
Occasional Contributor
Posts: 13

need help to write query on email send with base sas

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.

Super User
Posts: 7,827

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

Posted in reply to PriyaSaha

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

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

Posted in reply to KurtBremser

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.

Super User
Posts: 7,827

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

Posted in reply to PriyaSaha

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,975

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

Posted in reply to KurtBremser

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

Super User
Posts: 7,827

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor hbi
Contributor
Posts: 66

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

Posted in reply to KurtBremser

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.

Occasional Contributor
Posts: 13

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

Posted in reply to KurtBremser

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

Super User
Super User
Posts: 7,975

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

Posted in reply to PriyaSaha

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.

Ask a Question
Discussion stats
  • 8 replies
  • 374 views
  • 0 likes
  • 4 in conversation