DATA Step, Macro, Functions and more

Assign value of column name based on hierachy of column names

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Assign value of column name based on hierachy of column names

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.


Accepted Solutions
Highlighted
Solution
4 weeks ago
PROC Star
Posts: 831

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

Posted in reply to sunless07652
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;

View solution in original post


All Replies
Super User
Posts: 21,546

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

Posted in reply to sunless07652

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);
Occasional Contributor
Posts: 5

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

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.

Super User
Posts: 21,546

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

Posted in reply to sunless07652

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

 

new=catt('Var', put(index, z2.));
Highlighted
Solution
4 weeks ago
PROC Star
Posts: 831

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

Posted in reply to sunless07652
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;
Occasional Contributor
Posts: 5

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

Posted in reply to novinosrin

This is perfect, thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 161 views
  • 6 likes
  • 3 in conversation