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
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.