Hello,
I am a novice SAS programmer assisting research project, and I need help with data manipulation.
My data is as follows:
KIS | Stock | Name | Sum2000 | Sum2001 | Sum2002 | Sum2003 | Sum2004 | Sum2005 | Sum2006 | Sum2007 | Sum2008 | Sum2009 |
000292 | 059210 | AA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
000442 | 039020 | BB | 0 | 0 | 0 | 944079000 | 734022000 | 1093304000 | 320309000 | 1674600000 | 0 | 1699003000 |
000671 | 036480 | CC | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
001097 | 039230 | DD | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
I want to manipulate the data to look like as follows:
KIS | Stock | Name | Sum |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000292 | 059210 | AA | 0 |
000442 | 039020 | BB | 0 |
000442 | 039020 | BB | 0 |
000442 | 039020 | BB | 0 |
000442 | 039020 | BB | 944079000 |
000442 | 039020 | BB | 734022000 |
000442 | 039020 | BB | 1093304000 |
000442 | 039020 | BB | 320309000 |
000442 | 039020 | BB | 1674600000 |
000442 | 039020 | BB | 0 |
000442 | 039020 | BB | 1699003000 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
000671 | 036480 | CC | 0 |
In short words, after AA's Sum for Years 2000-2009 ends, then BB's Sum for Years 2000-2009 follows, and CC's Sum for Years 2000-2009 follows, and so on.
Could anyone provide SAS code for doing this?
It seems like I have to use proc transpose to do this, but I don't know how to duplicate rows along with proc transpose.
Since there are thousands of firms, I really need help with coding.
I'd highly appreciate anyone's help!
Please explain how this is any different than the question you asked in https://communities.sas.com/t5/SAS-Programming/Transposing-column-data-to-create-duplicate-rows/m-p/...
And why the proposed solutions weren't helpful. The first proposed solution would require exactly one character change to get this output.
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.