DATA Step, Macro, Functions and more

Compare columns and make one column

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Compare columns and make one column

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           


Accepted Solutions
Solution
‎10-11-2016 10:02 AM
Trusted Advisor
Posts: 1,378

Re: Compare columns and make one column

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


All Replies
Trusted Advisor
Posts: 1,378

Re: Compare columns and make one column

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; 

Contributor
Posts: 33

Re: Compare columns and make one column

This gives me only id's. Nothing else?

 

Super User
Posts: 6,938

Re: Compare columns and make one column

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   
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: Compare columns and make one column

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;
Trusted Advisor
Posts: 1,378

Re: Compare columns and make one column

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

 

Super User
Posts: 5,083

Re: Compare columns and make one column

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.

Super User
Posts: 6,938

Re: Compare columns and make one column


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,083

Re: Compare columns and make one column

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.

Solution
‎10-11-2016 10:02 AM
Trusted Advisor
Posts: 1,378

Re: Compare columns and make one column

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; 

Trusted Advisor
Posts: 1,378

Re: Compare columns and make one column

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; 

 

Super User
Super User
Posts: 7,401

Re: Compare columns and make one column

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; 
Super User
Posts: 5,083

Re: Compare columns and make one column

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

Super User
Posts: 6,938

Re: Compare columns and make one column


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,083

Re: Compare columns and make one column

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;

☑ This topic is SOLVED.

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

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