BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
user24
Obsidian | Level 7

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           

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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; 

View solution in original post

16 REPLIES 16
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

This gives me only id's. Nothing else?

 

Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Garnet | Level 18

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

 

Astounding
PROC Star

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
Super User

@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
PROC Star

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
Garnet | Level 18

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
Garnet | Level 18

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Super User

@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
PROC Star

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 3332 views
  • 4 likes
  • 5 in conversation