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!
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.
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.