topic Re: Assign value of column name based on hierachy of column names in SAS Programming

Assign value of column name based on hierachy of column names

sunless07652 — Mon, 29 Jan 2018 19:31:29 GMT

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.

Re: Assign value of column name based on hierachy of column names

Reeza — Mon, 29 Jan 2018 19:41:06 GMT

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);

Re: Assign value of column name based on hierachy of column names

novinosrin — Mon, 29 Jan 2018 19:41:58 GMT

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;

Re: Assign value of column name based on hierachy of column names

sunless07652 — Mon, 29 Jan 2018 19:59:40 GMT

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.

Re: Assign value of column name based on hierachy of column names

sunless07652 — Mon, 29 Jan 2018 20:00:03 GMT

This is perfect, thank you!

Re: Assign value of column name based on hierachy of column names

Reeza — Mon, 29 Jan 2018 20:00:48 GMT

Z2 format will add 0 in front and it's not an issue with @novinosrin solution.

new=catt('Var', put(index, z2.));