BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ClareRobin1
Calcite | Level 5

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:

 

Screen Shot 2018-04-05 at 04.37.36.png

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

Screen Shot 2018-04-05 at 04.45.15.png

 

Screen Shot 2018-04-05 at 04.54.08.png

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.

 

Please any ideas will help.

 

Thanks.

 

Code to generate the example data set is:

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Like this?

 

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

 

 

EMAIL RANK ORIGIN REASON
admin@gmail.com 3 USA Already in Canada
brin@yahoo.com 3 USA Already in Canada
llp@lit.com 2 Canada Already in Europe
llp@lit.com 3 USA Already in Europe

 

View solution in original post

1 REPLY 1
ChrisNZ
Tourmaline | Level 20

Like this?

 

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

 

 

EMAIL RANK ORIGIN REASON
admin@gmail.com 3 USA Already in Canada
brin@yahoo.com 3 USA Already in Canada
llp@lit.com 2 Canada Already in Europe
llp@lit.com 3 USA Already in Europe

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 333 views
  • 1 like
  • 2 in conversation