Create variable names from the values in other variables

[markc]

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!

[Tom]

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;

[markc]

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

[Tom]

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

[Astounding]

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.