Reshaping from data 1 to data 2

[Manoj Soni]

Hello
Greetings
I have two data sets , data 1 and data 2.
Data 1 is raw data, and data 2 is expected reshaped output.

Data 1, the last 16 columns has 16 different reading for a patient in one row in data 1, needs to be reshaped in 16 rows in data 2 with common variables.
Attached both data sets.
Many thanks and Regards
Manoj

[Ken Chui]

In future please use dataex to post data in code format directly. Downloading data files can be insecure so most users here do not open them. If you keep posting files as attachment, you may not get any help.

This is data 1, I dropped the variables that is not involved:

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 pid str10 sfr_ug str7 sfr_t str5 ser_ug str11 ser_t str8 sfl_ug str11 sfl_t str9 sel_ug str8 sel_t str6 lfr_ug str5 lfr_t str4 ler_ug str3 ler_t str8 lfl_ug str4 lfl_t str5 lel_ug str3 lel_t
"P155" "94.4" "97.0" "0.0" "2.0" "85.4" "84.0" "0.0" "3.0" "123.0" "126.0" "1.0" "1.0" "69.6" "68.0" "2.8" "2.0"
end

This is data 2, the goal:

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 pid str1(Body_position knee_position side) str2 measurement double value
"p155" "S" "F" "R" "UG" 94.4
"p155" "S" "F" "R" "T"    97
"p155" "S" "E" "R" "UG"    0
"p155" "S" "E" "R" "T"     2
"p155" "S" "F" "L" "UG" 85.5
"p155" "S" "F" "L" "T"    84
"p155" "S" "E" "L" "UG"    0
"p155" "S" "E" "L" "T"     3
"p155" "L" "F" "R" "UG"  123
"p155" "L" "F" "R" "T"   126
"p155" "L" "E" "R" "UG"    1
"p155" "L" "E" "R" "T"     1
"p155" "L" "F" "L" "UG" 69.6
"p155" "L" "F" "L" "T"    68
"p155" "L" "E" "L" "UG"  2.8
"p155" "L" "E" "L" "T"     2
end

And here is the code that may be able to achieve that:

keep pid sfr_ug-lel_t

foreach x of varlist sfr_ug-lel_t{
    rename `x' value`x'
}

reshape long value, i(pid) j(reading, string)
destring value, replace

replace reading = upper(reading)
split reading, gen(r) parse(_)
gen Body_position = substr(r1, 1, 1)
gen knee_position = substr(r1, 2, 1)
gen side = substr(r1, 3, 1)
rename r2 measurement
drop reading r1

order pid Body_position knee_position side measurement value

[Manoj Soni]

Thanks for the guidance. I will keep in mind next time not to attach files and use commands to show the scenarios. It is working. Thanks again. Regards
Manoj