BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

4 REPLIES 4
nehalsanghvi
Pyrite | Level 9

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.

fengyuwuzu
Pyrite | Level 9
Thank you, nehalsanghvi, for the new function. I never heard about it. Thanks a lot for letting me know.
art297
Opal | Level 21

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

 

fengyuwuzu
Pyrite | Level 9
Thank you, Art. This solution is more general.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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