topic Re: Create variable names from the values in other variables in New SAS User

Create variable names from the values in other variables

markc — Fri, 12 Apr 2019 03:57:57 GMT

Hello,

I am trying to create a new variable (called Payment_YearMonth) in each observation related to the value stored in YearMonth in each observation and store Amount as that variable's value.

For example:

HAVE:

YearMonth Amount

201607 10.00

201704 20.00

201713 30.00

201607 40.00

201704 50.00

201713 60.00

201607 70.00

201704 80.00

201713 90.00

WANT:

YearMonth	payment_201607	payment_201704	payment_201713
201607	$ 10.00
201704		$ 20.00
201713			$ 30.00
201607	$ 40.00
201704		$ 50.00
201713			$ 60.00
201607	$ 70.00
201704		$ 80.00
201713			$ 90.00

Any advice will be quickly responded to.

Thank you!

Re: Create variable names from the values in other variables

Tom — Fri, 12 Apr 2019 04:00:36 GMT

That looks like a useless format, but it is simple to get with PROC TRANSPOSE.

proc transpose data=have  out=want prefix=payment_;
  by yearmonth;
  id yearmonth;
  var amount;
run;

Re: Create variable names from the values in other variables

markc — Fri, 12 Apr 2019 05:05:11 GMT

Thanks so much, I tried sorting the data first by the BY variable but SAS is saying the ID value occurs twice in the same BY group. Do you know why that might be? Kind regards

Re: Create variable names from the values in other variables

Astounding — Fri, 12 Apr 2019 15:42:37 GMT

A slight adjustment is needed:

data have2;
set have;
rec = _n_;
run;

Then within PROC TRANSPOSE, change the BY statement:

by rec;

Do not change the ID statement.

Re: Create variable names from the values in other variables

Tom — Fri, 12 Apr 2019 11:56:18 GMT

Only you know your data. You might need to add a new variable to use for the BY grouping.
In your original example it looked like you wanted one output observation per input observation.
data for_transpose;
row+1;
set have;
run;
proc transpose data=for_transpose out=want;
by row;
...