topic Re: select max from multiple character variables for each obs in SAS Programming

select max from multiple character variables for each obs

fengyuwuzu — Tue, 28 Feb 2017 17:31:28 GMT

I have the following data, and want to "merge" the three columns into a new column, keeping the max ("YES") of the three columns.

MAX seems does not work. Any easier ways other than using three if statements (if var1="YES" then newvar=var1...)?

obs 	var1	    var2	var3
  1                              Yes
  2      Yes
  3                  Yes
  4                  Yes
  5                  Yes
  6                  Yes
  7                  Yes
  8                  Yes
  9                  Yes
 10                  Yes
 11                  Yes
 12                  Yes
 13      Yes
 14      Yes
 15      Yes
 16      Yes
 17                  Yes
 18                  Yes
 19                  Yes
 20                  Yes
 21      Yes
 22                  Yes
 23                  Yes
 24                  Yes
 25                  Yes
 26      Yes
 27                  Yes
 28                  Yes
 29                  Yes
 30                  Yes
 31                  Yes
 32      Yes
 33                  Yes
 34                  Yes
 35                  Yes
 36      Yes
 37                  Yes
 38                  Yes
 39                  Yes
 40                  Yes
 41                  Yes
 42                  Yes
 43                  Yes

Re: select max from multiple character variables for each obs

nehalsanghvi — Tue, 28 Feb 2017 17:50:30 GMT

This query will work if all you have is Yes and null values in all three columns:

select coalesce(var1,var2,var3) as NewVar
from mydataset;

Coalesce function takes the first non-null value it comes across in the three variables.

Re: select max from multiple character variables for each obs

art297 — Tue, 28 Feb 2017 18:09:35 GMT

If you're data includes more than values like Yes and missing, and you need to get the highest value that would result from a sort, you could use:

data want;
  set have;
  length var $3;
  array vars(3) $ var3-var1;
  call sortc(of vars(*));
  var=vars(3);
run;

Art, CEO, AnalystFinder.com

Re: select max from multiple character variables for each obs

fengyuwuzu — Tue, 28 Feb 2017 18:45:26 GMT

Thank you, nehalsanghvi, for the new function. I never heard about it. Thanks a lot for letting me know.

Re: select max from multiple character variables for each obs

fengyuwuzu — Tue, 28 Feb 2017 18:45:52 GMT

Thank you, Art. This solution is more general.