DATA Step, Macro, Functions and more

select max from multiple character variables for each obs

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

select max from multiple character variables for each obs

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

Accepted Solutions
Solution
‎02-28-2017 01:45 PM
PROC Star
Posts: 7,467

Re: select max from multiple character variables for each obs

Posted in reply to fengyuwuzu

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

 

View solution in original post


All Replies
Frequent Contributor
Posts: 75

Re: select max from multiple character variables for each obs

[ Edited ]
Posted in reply to fengyuwuzu

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.

Super Contributor
Posts: 318

Re: select max from multiple character variables for each obs

Posted in reply to nehalsanghvi
Thank you, nehalsanghvi, for the new function. I never heard about it. Thanks a lot for letting me know.
Solution
‎02-28-2017 01:45 PM
PROC Star
Posts: 7,467

Re: select max from multiple character variables for each obs

Posted in reply to fengyuwuzu

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

 

Super Contributor
Posts: 318

Re: select max from multiple character variables for each obs

Thank you, Art. This solution is more general.
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 171 views
  • 0 likes
  • 3 in conversation