Organizing data by date

[Sarah Parvanta]

Hello, My data file includes a functional health score, a breathing test score, and dates on which subjects submitted each of these scores. Currently the data are organized by 'time' of response within subject_id (first time responding, second time responding, etc.). I would like to line up responses by date within subject_id so that the date of the functional health score and the date of the breathing test score align. What would be the code to line up responses by date within subject_id? The data are copied in dataex format below along with a screenshot of the format I intend to create. (Please note that the long format of this data file, and the string format of the functional health scores and breathing test scores are deliberate.) Thanks!

* Example generated by -dataex-. For more info, type help dataex
clear
input int subject_id float(time function_response breath_response) str5 function_score str35 breath_score
 36 1 21958 21958 "35" "80" 
 36 2 22177 22177 "38" "84" 
 36 3 22200 22200 "38" "80" 
 68 1 21958 21958 "39" "92" 
 68 2 22189 22189 "39" "85" 
 68 3 22198 22349 "38" "91" 
 68 4 22349 22451 "38" "90" 
 68 5 22451 22470 "36" "89" 
 68 6 22470 22525 "39" ""   
 68 7 22525 22594 "38" "91" 
 68 8 22594 22620 "39" "90" 
 68 9 22620     . "38" ""   
329 1 21979 21979 "40" "98" 
329 2 22176 22176 "40" "100"
329 3 22449 22449 "37" "100"
329 4     . 22527 ""   "100"
333 1 21979 21979 "16" "59" 
333 2 22151 22151 "16" "59" 
333 3 22214 22214 "19" "49" 
333 4 22593 22435 "19" "49" 
333 5     . 22593 ""   "52" 
348 1 21979 21979 "37" "94" 
348 2 22178 22594 "33" ""   
348 3 22208     . "32" ""   
348 4 22594     . ""   ""   
end
format %td function_response
format %td breath_response

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input int subject_id float(time function_response breath_response) str5 function_score str35 breath_score
 36 1 21958 21958 "35" "80" 
 36 2 22177 22177 "38" "84" 
 36 3 22200 22200 "38" "80" 
 68 1 21958 21958 "39" "92" 
 68 2 22189 22189 "39" "85" 
 68 3 22198 22349 "38" "91" 
 68 4 22349 22451 "38" "90" 
 68 5 22451 22470 "36" "89" 
 68 6 22470 22525 "39" ""   
 68 7 22525 22594 "38" "91" 
 68 8 22594 22620 "39" "90" 
 68 9 22620     . "38" ""   
329 1 21979 21979 "40" "98" 
329 2 22176 22176 "40" "100"
329 3 22449 22449 "37" "100"
329 4     . 22527 ""   "100"
333 1 21979 21979 "16" "59" 
333 2 22151 22151 "16" "59" 
333 3 22214 22214 "19" "49" 
333 4 22593 22435 "19" "49" 
333 5     . 22593 ""   "52" 
348 1 21979 21979 "37" "94" 
348 2 22178 22594 "33" ""   
348 3 22208     . "32" ""   
348 4 22594     . ""   ""   
end
format  %tdN/D/CY function_response
format  %tdN/D/CY breath_response


replace function_response= breath_response if missing(function_response)
replace breath_response= function_response if missing(breath_response)
bys sub function_response (function_score): replace function_score= function_score[_N] if _N>1 & missing(function_score)
bys sub breath_response (breath_score): replace breath_score= breath_score[_N] if _N>1 & missing(breath_score)

Res.:

. sort sub time

. l, sepby(sub)

     +-----------------------------------------------------------------+
     | subjec~d   time   functio~se   breath_r~e   funct~re   breat~re |
     |-----------------------------------------------------------------|
  1. |       36      1   02/13/2020   02/13/2020         35         80 |
  2. |       36      2   09/19/2020   09/19/2020         38         84 |
  3. |       36      3   10/12/2020   10/12/2020         38         80 |
     |-----------------------------------------------------------------|
  4. |       68      1   02/13/2020   02/13/2020         39         92 |
  5. |       68      2   10/01/2020   10/01/2020         39         85 |
  6. |       68      3   10/10/2020   03/10/2021         38         91 |
  7. |       68      4   03/10/2021   06/20/2021         38         90 |
  8. |       68      5   06/20/2021   07/09/2021         36         89 |
  9. |       68      6   07/09/2021   09/02/2021         39            |
 10. |       68      7   09/02/2021   11/10/2021         38         91 |
 11. |       68      8   11/10/2021   12/06/2021         39         90 |
 12. |       68      9   12/06/2021   12/06/2021         38         90 |
     |-----------------------------------------------------------------|
 13. |      329      1   03/05/2020   03/05/2020         40         98 |
 14. |      329      2   09/18/2020   09/18/2020         40        100 |
 15. |      329      3   06/18/2021   06/18/2021         37        100 |
 16. |      329      4   09/04/2021   09/04/2021                   100 |
     |-----------------------------------------------------------------|
 17. |      333      1   03/05/2020   03/05/2020         16         59 |
 18. |      333      2   08/24/2020   08/24/2020         16         59 |
 19. |      333      3   10/26/2020   10/26/2020         19         49 |
 20. |      333      4   11/09/2021   06/04/2021         19         49 |
 21. |      333      5   11/09/2021   11/09/2021         19         52 |
     |-----------------------------------------------------------------|
 22. |      348      1   03/05/2020   03/05/2020         37         94 |
 23. |      348      2   09/20/2020   11/10/2021         33            |
 24. |      348      3   10/20/2020   10/20/2020         32            |
 25. |      348      4   11/10/2021   11/10/2021                       |
     +-----------------------------------------------------------------+

.

[Andrew Musau]

#2 fills in extra dates and needs some cleaning to match your wanted results. I think that this should do it, although do report any inconsistencies.

* Example generated by -dataex-. For more info, type help dataex
clear
input int subject_id float(time function_response breath_response) str5 function_score str35 breath_score
 36 1 21958 21958 "35" "80"
 36 2 22177 22177 "38" "84"
 36 3 22200 22200 "38" "80"
 68 1 21958 21958 "39" "92"
 68 2 22189 22189 "39" "85"
 68 3 22198 22349 "38" "91"
 68 4 22349 22451 "38" "90"
 68 5 22451 22470 "36" "89"
 68 6 22470 22525 "39" ""  
 68 7 22525 22594 "38" "91"
 68 8 22594 22620 "39" "90"
 68 9 22620     . "38" ""  
329 1 21979 21979 "40" "98"
329 2 22176 22176 "40" "100"
329 3 22449 22449 "37" "100"
329 4     . 22527 ""   "100"
333 1 21979 21979 "16" "59"
333 2 22151 22151 "16" "59"
333 3 22214 22214 "19" "49"
333 4 22593 22435 "19" "49"
333 5     . 22593 ""   "52"
348 1 21979 21979 "37" "94"
348 2 22178 22594 "33" ""  
348 3 22208     . "32" ""  
348 4 22594     . ""   ""  
end
format  %tdN/D/CY function_response
format  %tdN/D/CY breath_response

expand 2, g(new)
replace function_response= breath_response if new
replace function_score = breath_score if new
drop breath_score breath_response
sort subject_id function_response new
bysort subject_id function_response (time): replace time= time[_N]
drop if missing(function_response)
reshape wide function_response function_score, i(subject_id time) j(new)
rename (function_response0 function_score0 function_response1 function_score1) (function_response function_score breath_response breath_score)
sort subject_id time

Res.:

. l, sepby(sub)

     +-----------------------------------------------------------------+
     | subjec~d   time   functio~se   funct~re   breath_r~e   breat~re |
     |-----------------------------------------------------------------|
  1. |       36      1   02/13/2020         35   02/13/2020         80 |
  2. |       36      2   09/19/2020         38   09/19/2020         84 |
  3. |       36      3   10/12/2020         38   10/12/2020         80 |
     |-----------------------------------------------------------------|
  4. |       68      1   02/13/2020         39   02/13/2020         92 |
  5. |       68      2   10/01/2020         39   10/01/2020         85 |
  6. |       68      3   10/10/2020         38            .            |
  7. |       68      4   03/10/2021         38   03/10/2021         91 |
  8. |       68      5   06/20/2021         36   06/20/2021         90 |
  9. |       68      6   07/09/2021         39   07/09/2021         89 |
 10. |       68      7   09/02/2021         38   09/02/2021            |
 11. |       68      8   11/10/2021         39   11/10/2021         91 |
 12. |       68      9   12/06/2021         38   12/06/2021         90 |
     |-----------------------------------------------------------------|
 13. |      329      1   03/05/2020         40   03/05/2020         98 |
 14. |      329      2   09/18/2020         40   09/18/2020        100 |
 15. |      329      3   06/18/2021         37   06/18/2021        100 |
 16. |      329      4            .              09/04/2021        100 |
     |-----------------------------------------------------------------|
 17. |      333      1   03/05/2020         16   03/05/2020         59 |
 18. |      333      2   08/24/2020         16   08/24/2020         59 |
 19. |      333      3   10/26/2020         19   10/26/2020         49 |
 20. |      333      4            .              06/04/2021         49 |
 21. |      333      5   11/09/2021         19   11/09/2021         52 |
     |-----------------------------------------------------------------|
 22. |      348      1   03/05/2020         37   03/05/2020         94 |
 23. |      348      2   09/20/2020         33            .            |
 24. |      348      3   10/20/2020         32            .            |
 25. |      348      4   11/10/2021              11/10/2021            |
     +-----------------------------------------------------------------+

.

[Sarah Parvanta]

Thank you Andrew Musau. I will try this out!