Help using Base SAS procedures

keep only unique values for a row on multiple column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

keep only unique values for a row on multiple column

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


Accepted Solutions
Solution
‎06-23-2014 11:54 AM
Regular Contributor
Posts: 217

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

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.

View solution in original post


All Replies
Super User
Posts: 5,255

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

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;

Data never sleeps
Super User
Super User
Posts: 7,392

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

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;

Solution
‎06-23-2014 11:54 AM
Regular Contributor
Posts: 217

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

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.

Super User
Posts: 5,079

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

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.

Occasional Contributor
Posts: 11

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

Thank you everybody for your precious help

Super User
Posts: 9,671

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1572 views
  • 3 likes
  • 6 in conversation