topic Re: Compare columns and make one column in SAS Programming

Compare columns and make one column

user24 — Mon, 10 Oct 2016 12:40:31 GMT

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

Re: Compare columns and make one column

Shmuel — Mon, 10 Oct 2016 12:49:58 GMT

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;

Re: Compare columns and make one column

RW9 — Mon, 10 Oct 2016 12:49:59 GMT

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;

Re: Compare columns and make one column

Shmuel — Mon, 10 Oct 2016 12:53:54 GMT

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

Re: Compare columns and make one column

Astounding — Mon, 10 Oct 2016 13:19:45 GMT

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.

Re: Compare columns and make one column

Kurt_Bremser — Mon, 10 Oct 2016 13:55:50 GMT

@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.

Re: Compare columns and make one column

Astounding — Mon, 10 Oct 2016 14:59:33 GMT

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.

Re: Compare columns and make one column

Shmuel — Mon, 10 Oct 2016 18:07:09 GMT

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;

Re: Compare columns and make one column

Shmuel — Mon, 10 Oct 2016 19:00:25 GMT

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;

Re: Compare columns and make one column

Astounding — Mon, 10 Oct 2016 19:36:10 GMT

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;

Re: Compare columns and make one column

Kurt_Bremser — Tue, 11 Oct 2016 06:37:06 GMT

@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.

Re: Compare columns and make one column

RW9 — Tue, 11 Oct 2016 08:33:13 GMT

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;

Re: Compare columns and make one column

Astounding — Tue, 11 Oct 2016 11:37:15 GMT

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

Re: Compare columns and make one column

user24 — Tue, 11 Oct 2016 13:57:35 GMT

This gives me only id's. Nothing else?

Re: Compare columns and make one column

Kurt_Bremser — Tue, 11 Oct 2016 14:03:51 GMT

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

Re: Compare columns and make one column

Astounding — Tue, 11 Oct 2016 15:27:17 GMT

OK, looks like you didn't heed my warning. You accepted a solution as correct when it can give the wrong answer:

Whenever class5 is missing
Whenever two incoming variables are missing (not including the combination of class1 and class2 being missing)

Maybe these situations won't occur in your data, but check them!

Re: Compare columns and make one column

Shmuel — Tue, 11 Oct 2016 15:36:05 GMT

@Astounding you are absolutely right.

The code should be:

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 and

clx(i) ne ' '

then class = clx(i);

end;

keep id class;

run;