Transpose several columns to one column

BC01 — Sat, 12 Nov 2022 02:50:34 GMT

I have some simplified dummy data and desired results below. The columns are ID, Company, FiscalYear, Revenue, Net Income, and Expenses. I want to transpose the values of Revenue, Net Income, and Expenses into one column called Values, with another column called Item that indicates which financial item it is. I want to keep the other columns. Below is the desired result. The exact order/sorting is not required. Notice that sometimes there is NULL data (eg. C 2020), or yearly data is absent (eg. B 2020).

How can I implement this?

Original Data Set

ID	Company	FiscalYear	Revenue	Net Income	Expenses
1	A	2019	982.589	886.813	95.776
2	B	2019	807.335	727.985	79.350
3	C	2019	1,128.979	1,024.482	104.497
1	A	2020	1,005.182	909.667	95.515
3	C	2020	.	.	.
1	A	2021	612.943	555.705	57.238
2	B	2021	898.566	812.447	86.119
3	C	2021	640.070	577.160	62.910

Desired Result

ID	Company	FiscalYear	Item	Value
1	A	2019	Revenue	982.589
1	A	2019	Net Income	886.813
1	A	2019	Expenses	95.776
2	B	2019	Revenue	807.335
2	B	2019	Net Income	727.985
2	B	2019	Expenses	79.350
3	C	2019	Revenue	1,128.979
3	C	2019	Net Income	1,024.482
3	C	2019	Expenses	104.497
1	A	2020	Revenue	1,005.182
1	A	2020	Net Income	909.667
1	A	2020	Expenses	95.515
3	C	2020	Revenue	.
3	C	2020	Net Income	.
3	C	2020	Expenses	.
1	A	2021	Revenue	612.943
1	A	2021	Net Income	555.705
1	A	2021	Expenses	57.238
2	B	2021	Revenue	898.566
2	B	2021	Net Income	812.447
2	B	2021	Expenses	86.119
3	C	2021	Revenue	640.070
3	C	2021	Net Income	577.160
3	C	2021	Expenses	62.910

Re: Transpose several columns to one column

Tom — Sat, 12 Nov 2022 04:11:27 GMT

That is what PROC TRANSPOSE is for.

Looks like your key variables ID, COMPANY and FISCALYEAR uniquely identify the observations in your original dataset.

So use those in the BY statement. If the data is not sorted by those variables yet you can add the NOTSORTED keyword to the BY statement.

List the other three variables in the VAR statement.

You can use the NAME= option to control the name given for the variable that has the name of the original variable.

You can use the RENAME= dataset option to rename the output variable.

proc tranpose data=HAVE name=ITEM out=WANT(rename=(col1=VALUE)) ;
  by ID Company FiscalYear NOTSORTED ;
  var Revenue NetIncome Expenses ;
run;

topic Re: Transpose several columns to one column in SAS Enterprise Guide

Transpose several columns to one column

Re: Transpose several columns to one column