Compare columns and make one column

[user24]

How can i  compare lots of columns to get which one first alphabetically? I will give 5 columns example. 

Here is my table :

id      class1    class2   class3   class4   class5

27      A            B           A                        C

33      B            C          D           B            

46      A            A                        C            A

I need   this table :

id    class

27    A

33    B

46    A           

[Shmuel]

data want;

   set have;

        array clx class1-class5 ;  

        class = ' ';

        do i=1 to dim(clx);

           if  class = ' ' and clx(i) ne ' ' then class = clx(i);

           else class = min(class, clx(i));

       end;

      keep id class_out;

run; 

[user24]

This gives me only id's. Nothing else?

[Kurt_Bremser]

You probably did not copy the code correctly:

data have;
infile cards dlm=',' dsd;
input id class1 $ class2 $ class3 $ class4 $ class5 $;
cards;
27,A,B,A,,C
33,B,C,D,B,
46,A,A,,C,A
;
run;

data want1;
set have;
array classes {5} class1-class5;
do _n_=1 to 5;
   if class=' '  or (' ' < classes{_n_} < class) then class=classes{_n_};
end;
run;

proc print;run;

data want2;
  id=1; class1="B"; class2="D"; class3=""; class4="A"; class5="F";
  call sortc(of class:);
  result=char(coalescec(of class:),1);
run; 

proc print;run;

The results are

  Obs    id    class1    class2    class3    class4    class5    class

   1     27      A         B         A                   C         A  
   2     33      B         C         D         B                   B  
   3     46      A         A                   C         A         A  

 Obs    id    class1    class2    class3    class4    class5    result

  1      1                A         B         D         F         A   

[RW9]

Posting test data in the form of a datastep would allow us to give working code.  At a guess:

data want;
  set have;
  array class{5};
  result=class{1}
  do i=2 to 5;
    if class{i} ne "" and class{i} < result then result=class{i};
  end;
run;

[Shmuel]

to @RW9 - in case that class(1) is Blank the final result will be Blank too;

[Astounding]

SQL makes this even easier:

proc sql;

create table want as select id, min(class1, class2, class3, class4, class5) as class

from have;

quit;

The MIN function in SQL ignores missing values, and works for character as well as numeric arguments.  You may be able to abbreviate this as min(of class1-class5), but I haven't tested to see if this works in PROC SQL.

[Kurt_Bremser]

---

@Astounding wrote:

The MIN function in SQL ignores missing values, and works for character as well as numeric arguments.

---

Has that been added in 9.3 or 9.4? In 9.2 min() only accepts numerical arguments.

[Astounding]

Kurt,

I'm not sure when MIN became available for character fields, but it is more limited than I had suspected.  It does NOT work with SAS data sets!  It does work when the source data is a Greenplum table, which means you may not be able to get it to work at all.

Sorry about that.

[Shmuel · Accepted solution]

I have checked SAS 9.2 O/L doc. and SAS 9.4 as SAS Studio UE:

In both cases. MIN function desires numeric constants/variables/expressions.

Thats right even in SQL expression as: 

  proc sql; select min(a,b) from test; quit;   /*  where a="A"; b="B";  */

I got desired result running:

  proc sql; select byte(min(rank(a) , rank(b))); quit;

One exception: BYTE and RANK functions desire one character string only;

In case of more characters, those functions relate to the first character only.

To the specific query:

data want;

   set have;

        array clx class1-class5 ;  

        class = ' ';

        do i=1 to dim(clx);

           if  class = ' ' and clx(i) ne ' ' then class = clx(i);

           else if clx(i) < class then class = clx(i);

       end;

      keep id class;

run; 

[Shmuel]

Other solution based on sorting character array:

data want;

   set have;

        array clx class1-class5 ;  

        call sortc(clx(*));

        class = clx(1);       

        keep id class;

run; 

[RW9]

I do like the thinking on this post, really should use those call functions more often very useful.  Anyways, I tried to run that and found a few problems.  The (*) causes an error, and using dim() doesn't sort.  But the idea is perfectly sound and this does work (note I put the coalescec to avoid possile missings):

data want;
  id=1; class1="B"; class2="D"; class3=""; class4="A"; class5="F";
  call sortc(of class:);
  result=char(coalescec(of class:),1);
run; 

[Astounding]

Just a follow-up note:  This idea works.  It was posted after I posted my comment about all the previous solultions having a flaw.

[Kurt_Bremser]

---

@Astounding wrote:

Kurt,

I'm not sure when MIN became available for character fields, but it is more limited than I had suspected.  It does NOT work with SAS data sets!  It does work when the source data is a Greenplum table, which means you may not be able to get it to work at all.

Sorry about that.

---

I guess it works there because SAS simply hands it off to the implicit passthrough.

[Astounding]

user24,

This is actually quite unusual, but every single solution presented (mine included) has some sort of flaw in it.  Here's my second attempt:

data want;

set have;

array classes {5} class1-class5;

do _n_=1 to 5;

   if class=' '  or (' ' < classes{_n_} < class) then class=classes{_n_};

end;

run;