DATA Step, Macro, Functions and more

return a value by matching a cell with column name

Accepted Solution Solved
Reply
Learner
Posts: 1
Accepted Solution

return a value by matching a cell with column name

[ Edited ]

I have a large data set with a bunch of redundant values in many columns.  each column corresponds to a variable, and repeats the same value for the other remaining variables like below;

COLUMN 1COLUMN 2 ends in _xCOLUMN 3ends in _yCOLUMN 4 ends in _w
x112 3
y112 3
z112 3
w112 3
x251 4
y251 4
z251 4
w251 4
a251 4

x,y,z,w,a, etc. are the variables.
where x1,y1,z1,w1 are part data for customer 1 and x1,y2,z2,w2,a2 are data of customer 2.

column 1 is the value for variable x, and it's value repeats for the rest of the variables for that customer.

 

How can I aggregate these into one column for each variable for the customer like below;

COLUMN 1AGGREGATE COLUMN
x11
y12
z1
w13
x25
y21
z2
w2
a24


I'm not sure how to tell SAS to serach the column name for the variable, and return just that value.


Accepted Solutions
Solution
Wednesday
Valued Guide
Posts: 590

Re: return a value by matching a cell with column name

You can use FIND() function to search the values of a variable and then create a new variable based on that.

 

if find(column1,'x','i') then new_col=column_x;
else if find(column1,'y','i') then new_col=column_y;
...................so on.
Thanks,
Suryakiran

View solution in original post


All Replies
Solution
Wednesday
Valued Guide
Posts: 590

Re: return a value by matching a cell with column name

You can use FIND() function to search the values of a variable and then create a new variable based on that.

 

if find(column1,'x','i') then new_col=column_x;
else if find(column1,'y','i') then new_col=column_y;
...................so on.
Thanks,
Suryakiran
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 73 views
  • 0 likes
  • 2 in conversation