topic Re: Reshaping data from long to wide in SAS Programming

Reshaping data from long to wide

jlee8 — Wed, 04 Nov 2020 22:35:13 GMT

Hello,

I'm not sure if SAS does support a manipulation like this but if there is a way, please advise.

I have a dataset that look like the following.

From

CLM	DOS	FEE	PROC	PD	CT
1	1	9	A	3	1
2	1	10	B	3	4
2	2	11	B	1	6
2	2	11	C	4	3
3	1	12	A	4	8
3	1	12	B	8	5
3	2	13	A	5	1
3	2	13	C	6	4
3	3	14	B	3	3

And I want to turn it into this:

To

CLM	DOS	FEE	PROC1	PD1	CT1	PROC2	PD2	CT2
1	1	9	A	3	1
2	1	10	B	3	4
2	2	11	B	1	6	C	4	3
3	1	12	A	4	8	B	8	5
3	2	13	A	5	1	C	6	4
3	3	14	B	3	3

I've tried PROC TRANSPOSE to no avail.

My question is how do you turn that "From" table to the "To" table? If there is no simple function to do it, what would be a good step by step approach?

Thank you for your help.

Re: Reshaping data from long to wide

ballardw — Wed, 04 Nov 2020 22:51:15 GMT

For SAS the question becomes more of a "what can you do with data in that shape than the current form".

And second, do you really need a data set for further manipulation or is this for people to read, i.e. a report?

You would also need to carefully describe the rules you have for manipulating the data. I can't spot anything that actually makes sense.

Re: Reshaping data from long to wide

Reeza — Wed, 04 Nov 2020 23:21:43 GMT

The transpose procedures or a data step will work fine but in Proc transpose will have to do it multiple times and join the results.

See the second example in the first and second links below that illustrate how to do it either via proc transpose or a data step.

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

@jlee8 wrote:

Hello,

I'm not sure if SAS does support a manipulation like this but if there is a way, please advise.

I have a dataset that look like the following.

From

CLM DOS FEE PROC PD CT

1 1 9 A 3 1

2 1 10 B 3 4

2 2 11 B 1 6

2 2 11 C 4 3

3 1 12 A 4 8

3 1 12 B 8 5

3 2 13 A 5 1

3 2 13 C 6 4

3 3 14 B 3 3

And I want to turn it into this:

To

CLM DOS FEE PROC1 PD1 CT1 PROC2 PD2 CT2

1 1 9 A 3 1

2 1 10 B 3 4

2 2 11 B 1 6 C 4 3

3 1 12 A 4 8 B 8 5

3 2 13 A 5 1 C 6 4

3 3 14 B 3 3

I've tried PROC TRANSPOSE to no avail.

My question is how do you turn that "From" table to the "To" table? If there is no simple function to do it, what would be a good step by step approach?

Thank you for your help.

J