topic keep only unique values for a row on multiple column in SAS Procedures

keep only unique values for a row on multiple column

Fabio — Mon, 23 Jun 2014 14:39:57 GMT

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

Re: keep only unique values for a row on multiple column

LinusH — Mon, 23 Jun 2014 14:51:35 GMT

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;

Re: keep only unique values for a row on multiple column

RW9 — Mon, 23 Jun 2014 15:06:01 GMT

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;

Re: keep only unique values for a row on multiple column

jwillis — Mon, 23 Jun 2014 15:54:31 GMT

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.

Re: keep only unique values for a row on multiple column

Astounding — Mon, 23 Jun 2014 15:56:49 GMT

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.

Re: keep only unique values for a row on multiple column

Fabio — Tue, 24 Jun 2014 08:35:56 GMT

Thank you everybody for your precious help

Re: keep only unique values for a row on multiple column

Ksharp — Tue, 24 Jun 2014 14:36:39 GMT

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