keep only unique values for a row on multiple column

[Fabio]

Hi,

I need to keep only unique values for a row on multiple column.

Here is an example:

Input:

Name, Language1,Language2,Language1

Tom, EN,EN,IT

MARK, ES,ES,EN

Desired Output:

Name, Language1,Language2,Language1

Tom, EN,IT,''

MARK, ES,EN,''

Thank you in advance for your help.

Fabio

[LinusH]

As in most analysis situations, it's better to have data values in rows rather than in columns.

By transposing your data, you can then use standard procedures, such as SQL, SORT:

select distinct name, language

from want;

[RW9]

You could also use arrays on such data.  The below would give you a space delimited list of distinct languages.

data have;
  attrib Name L1 L2 L3 format=$200.;
  name="Tom"; l1="EN";l2="EN"; l3="IT"; output;
  name="MARK"; l1="ES"; l2="ES"; l3="EN"; output;
run;

data want (drop=i);
  set have;
  attrib result format=$2000.;
  array l{3};
  do i=1 to 3;
    if index(result,l{i})=0 then result=strip(result)||" "||strip(l{i});
  end;
run;

[jwillis · Accepted solution]

Fabio,

I am assuming that you meant to type Language1, Language2 and Language3.  You can drop the input names in the datastep and then rename the lang1 - lang3 variables to be the original Language names.

proc sql; drop table work.have; quit;
data have;
format name $16. Language1 $2. Language2 $2. Language3 $2.;
input name Language1 Language2 Language3;
datalines;
Tom EN EN IT
MARK ES ES EN
;
run;

proc sql; drop table work.want; quit;
data want;
   set have;
   array lanz{3} $ language1 language2 language3;
   array lano{3} $2 lang1 lang2 lang3 (3*'');
  
   do k=1 to 3;
     lano(k) = ' ';
        do j=1 to 3;
            if j> 1 then do;
            if lanz(k) not = ' ' and lanz(k) not = lano(j-1) and lano(j) = ' ' then lano(j) = lanz(k);
            end;
            else do;
            if lanz(k) not = ' ' and lano(j) = ' ' then lano(j) = lanz(k);
            end;
        end;
   end;
run;

proc print data=want;
run;

Message was edited by: James Willis  If the language(n) needs to be checked in all lang(n) fields, then the do statement will need to be built differently.

[Astounding]

If you really only have 3 variables, you could just do it the simple way.

data want;

set have;

if language2=language1 then language2=' ';

if language3 = language2 then language3=' ';

if language3=language1 then language3=' ';

if language2=' ' then language2=language3;

run;

If you have more than 3 variables, then arrays might be a better choice.

[Fabio]

Thank you everybody for your precious help

[Ksharp]

data have;
format name $16. Language1 $2. Language2 $2. Language3 $2.;
input name Language1 Language2 Language3;
datalines;
Tom EN EN IT
MARK ES ES EN
;
run;
data want(drop=n i);
 set have;
 array lan{*} $ lang: ;
 array x{100} $ 32 _temporary_;
 n=0;
 do i=1 to dim(lan);
   if lan{i} not in x then do;n+1;x{n}=lan{i};end;
 end;
 call missing(of lan{*});
 do i=1 to n;
   lan{i}=x{i};
 end;
run;
  

Xia Keshan