## How do i do complex by group processing?

Solved
Occasional Contributor
Posts: 7

# How do i do complex by group processing?

Hi all,

I have a question regarding the table below (code used to generate it is at bottom of page). My objective is to do a deduplication by email based on the conditions below:

- if there are repeating values of email, priority is given to the origin with the highest rank (where 1=highest , 3=lowest);

- If an email with a rank of 1 is picked over one with a rank of 2, create another data set with a new variable - REASON - dropped since email exist in Europe:

So the two output tables are desired. One contained the emails picked and another containing the emails dropped as follows:

Here are the steps I have taken to get this done:

- sort by email and rank

- then output first record only for each by group

Codes used are below: But how do I create the desired drop out reason table above please?

proc sort data=t1;
by email rank;
run;

data t2;
set t1;
by email rank;
if first.email;
run;

I have been able to create the first desired table above but dont know how to go about creating the drop out reason table.

Thanks.

Code to generate the example data set is:

data t1;
length email \$18 origin \$8;
input email \$ rank origin \$;
datalines;
llp@lit.com 1 Europe
llp@lit.com 3 USA
brin@yahoo.com 3 USA
;

Accepted Solutions
Solution
‎04-05-2018 02:32 AM
PROC Star
Posts: 2,370

## Re: How do i do complex by group processing?

[ Edited ]

Like this?

``````data HAVE(index=(A=(EMAIL RANK )));
length EMAIL \$18 ;
input EMAIL \$ RANK ORIGIN \$;
datalines;
llp@lit.com 1 Europe
llp@lit.com 3 USA
brin@yahoo.com 3 USA
run;
data PICKED(drop=REASON) DROPPED;
set HAVE;
by EMAIL ;
length REASON \$20;
retain REASON;
if first.EMAIL then do;
output PICKED;
end;
else output DROPPED;
run;
``````

All Replies
Solution
‎04-05-2018 02:32 AM
PROC Star
Posts: 2,370

## Re: How do i do complex by group processing?

[ Edited ]

Like this?

``````data HAVE(index=(A=(EMAIL RANK )));
length EMAIL \$18 ;
input EMAIL \$ RANK ORIGIN \$;
datalines;
llp@lit.com 1 Europe
llp@lit.com 3 USA
brin@yahoo.com 3 USA
run;
data PICKED(drop=REASON) DROPPED;
set HAVE;
by EMAIL ;
length REASON \$20;
retain REASON;
if first.EMAIL then do;
output PICKED;
end;
else output DROPPED;
run;
``````