Hi I have a table with a series of variables that have 1 or ' ' as values. The variable are named as var1, var2, ..., var16. If we just use var1-3 as example here, I want to create a new variable 'new' that has value of the variable name that has 1, but if a row has 2 or more variables with 1, then pick the smaller variable number, for example like this table:
Var1 Var2 Var3 new
1 1 Var1
1 Var3
1 1 1 Var1
1 1 Var2
Thank you.
data have;
input Var1 Var2 Var3 ;
datalines;
1 . 1
. . 1
1 1 1
. 1 1
;
data want;
set have;
array t(*) var:;
k=whichn(1, of t[*]);
new=vname(t(k));
drop k;
run;
Are your variable names var1, var2, var3 etc?
If so, look at the WHICHC/N functions which return the first index of the value you're looking for, assuming numeric it would look something like:
index = whichn(1, of var1-var16);
new=catt('Var', index);
This is great. Actually the variable are named TAPQ01-TAPQ16, so there's the 0 in front of 1-9, but I can just create a new set of variables named TAPQ1-TAPQ16, or just list them one by one. Thank you.
Z2 format will add 0 in front and it's not an issue with @novinosrin solution.
new=catt('Var', put(index, z2.));
data have;
input Var1 Var2 Var3 ;
datalines;
1 . 1
. . 1
1 1 1
. 1 1
;
data want;
set have;
array t(*) var:;
k=whichn(1, of t[*]);
new=vname(t(k));
drop k;
run;
This is perfect, thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.