Reshaping data from long to wide

[jlee8]

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

[ballardw]

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.

[Reeza · Accepted solution]

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

---