Naming columns using values from data table

Question

Let's say I have two columns A and B.

A     B
12    "randstr"
39    "randstr"
2     "randstr"

This random string is repeated in each row.

I'm interested in how I can get the table below:

randstr     B
12          "randstr"
39          "randstr"
2           "randstr"

The value in the column B was used to rename column A. I have tried using rename and all sorts of macro magic but failed. I have no idea how to proceed.

I've tried the answers below and they just don't allow for reading the value from the data and then using the value as a column name:

Is this a homework question, this is not the first time today I've seen it/ Anyways, you're looking for how to create a format. Google a paper called: PROC FORMAT not just another pretty face and it has some really good examples. — Reeza
– Reeza, Commented Feb 14, 2018 at 20:46
If you want actual code help, your question doesn't follow SO rules, you need to show what you've tried, not just say you've tried something. — Reeza
– Reeza, Commented Feb 14, 2018 at 20:47
@Reeza I'm pretty sure PROC FORMAT won't work. String is random, I cannot list all of the values. I've added 3 answers I've tried. It's also not homework, I'm doing this at work and have just started to play with macros and thought there might be a way. Could you link me to the question that you've seen repeated? — Looft
– Looft, Commented Feb 15, 2018 at 6:18

Richard · Accepted Answer · 2018-02-15 09:45:53Z

1

The transformation could also be seen as a row-wise transposition.

data have;
attrib A length=8 B length=$32;
row+1;
input
A   & B; datalines;
12    xyz-123-abc
39    xyz-123-abc
2     xyz-123-abc
run;

proc transpose data=have out=want(drop=row _name_);
  by row;
  var A;
  id B;
  copy B;
run;

In non-toy scenarios the B column is often not a single value. Try the same transpose with data having variation in B. The procedure will create two new columns from the values of B.

A   & B; datalines;
12    xyz-123-abc
39    xyz-123-abc
2     xyz-123-abc
3141  xyz-456-def

answered Feb 15, 2018 at 9:45

Richard

27.6k4 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Allan Bowe · Accepted Answer · 2018-02-14 21:28:50Z

0

Using this macro, it's fairly straightforward:

/* get first value in the dataset */
%let new_col=%mf_getvalue(work.YOURDATA,B);

/* rename variable A */
proc datasets library=work nolist;
 modify YOURDATA;
 rename A=%sysfunc(dequote(&new_col));
quit;

answered Feb 14, 2018 at 21:28

Allan Bowe

12.8k20 gold badges83 silver badges126 bronze badges

1 Comment

Looft Over a year ago

Works! Exactly what I've needed. Thanks!

Collectives™ on Stack Overflow

Naming columns using values from data table

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related