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
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
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.