Your description of what you want is not clear. Do you want to pick just one email address? If not do you want to write a separate observation for each unique email address?
Also what are the rules for detecting duplicate email addresses? Are the email addresses case sensitive? That is do you to treat "Joe@gmail.com" and "JOE@GMAIL.COM" as one email address or two?
I do not see any difference between your input and your output data. Also what is with the periods in you data display?
Let's make some example data. Let's keep some of those periods in the data.
data have;
infile cards dsd truncover;
input (email_1-email_5) (:$char100.) ;
cards;
TESTING@NOWHERE.ORG,,TESTING@NOWHERE.ORG,,
,.,TESTING1@NOWHERE.ORG,.,
TESTING2@NOWHERE.ORG,TESTING2@NOWHERE.ORG,,TESTING2@NOWHERE.ORG,
TESTING3@NOWHERE.ORG,,.,TESTING3@NOWHERE.ORG,TESTING3@NOWHERE.ORG
;
Now let's loop over the source variables and only put new values into the temporary array.
Then we can either concatenate the values into a single LOOONG variable.
data want;
length email_list $500 ;
set have ;
array emails email_1-email_5;
array temp [5] $100 _temporary_;
do index=1 to dim(emails);
if compress(emails[index],,'kad') ne ' ' then do;
if emails[index] not in temp then temp[index]=emails[index];
end;
end;
email_list = catx(';', of temp[*]) ;
drop index ;
run;
Obs email_list email_1 email_2
1 TESTING@NOWHERE.ORG TESTING@NOWHERE.ORG
2 TESTING@NOWHERE.ORG;TESTING1@NOWHERE.ORG .
3 TESTING2@NOWHERE.ORG;TESTING1@NOWHERE.ORG TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG
4 TESTING3@NOWHERE.ORG;TESTING1@NOWHERE.ORG TESTING3@NOWHERE.ORG
Obs email_3 email_4 email_5
1 TESTING@NOWHERE.ORG
2 TESTING1@NOWHERE.ORG .
3 TESTING2@NOWHERE.ORG
4 . TESTING3@NOWHERE.ORG TESTING3@NOWHERE.ORG
Or into multiple observations.
data want;
length email_number 8 email $100 ;
set have ;
array emails email_1-email_5;
array temp [5] $100 _temporary_;
do index=1 to dim(emails);
if compress(emails[index],,'kad') ne ' ' then do;
if emails[index] not in temp then temp[index]=emails[index];
end;
end;
email_number=0;
do index=1 to dim(temp);
if temp[index] ne ' ' then do;
email_number + 1;
email = temp[index];
output;
end;
end;
drop index;
run;
Result
e
m
a
i
l
_ e e e e e
n m m m m m
u e a a a a a
m m i i i i i
O b a l l l l l
b e i _ _ _ _ _
s r l 1 2 3 4 5
1 1 TESTING@NOWHERE.ORG TESTING@NOWHERE.ORG TESTING@NOWHERE.ORG
2 1 TESTING@NOWHERE.ORG . TESTING1@NOWHERE.ORG .
3 2 TESTING1@NOWHERE.ORG . TESTING1@NOWHERE.ORG .
4 1 TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG
5 2 TESTING1@NOWHERE.ORG TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG TESTING2@NOWHERE.ORG
6 1 TESTING3@NOWHERE.ORG TESTING3@NOWHERE.ORG . TESTING3@NOWHERE.ORG TESTING3@NOWHERE.ORG
7 2 TESTING1@NOWHERE.ORG TESTING3@NOWHERE.ORG . TESTING3@NOWHERE.ORG TESTING3@NOWHERE.ORG
