How to transform data from long to wide format

[Hale Isaac]

Hello,

I want to convert my dataset into wide format, with one row per child, and columns representing their placement for each day. The example data (below) ranges from 01 October 2010 – 31March 2021. Counting the start and end date, I have 3864 days.

For children who are not observed for the full 3864 days, or who enter after the start date, would have missing values (i.e. no placement) for those days. I am not sure how to transform the data from long to wide format to achieve this.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ID int(startdate enddate duration) str8 place
1 18536 18560   24 "foster"  
1 18560 18561    1 "foster"  
1 18561 19023  462 "foster"  
2 19443 19472   29 "foster"  
2 19472 19620  148 "foster"  
2 19620 19786  166 "foster"  
2 19786 20930 1144 "adoption"
3 19964 20298  334 "foster"  
3 20298 20314   16 "parent"  
4 18710 18772   62 "foster"  
4 18772 19306  534 "parent"  
4 19306 22370 3064 "adopted" 
end
format %tdnn/dd/CCYY startdate
format %tdnn/dd/CCYY enddate

[Nick Cox]

What are you going to do with those thousands of new variables? I don't see a rationale for wide layout.

[Rich Goldstein]

to expand a little on Nick's comment in #2: almost everything in Stata, whether for analysis or data management or graphics, is easier in long format than in wide format