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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.