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
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;
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;
This gives me only id's. Nothing else?
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
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;
to @RW9 - in case that class(1) is Blank the final result will be Blank too;
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.
@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.
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 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;
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;
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;
Just a follow-up note: This idea works. It was posted after I posted my comment about all the previous solultions having a flaw.
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: