[Fatimah Gh]

it worked, you helped a lot! thank you.

[Sarah Mj]

this is part of my data i only extracted D14 (day14) although I have others that are similar to this but with different days.
And i wanted it to reshape it long, like MRN/D14 /condition 1/grade1 (D14HTNaftertherapy D14GradeofHTN), condition 2/grade2(D14HTNaftertherapy D14GradeofHTN), condition3/grade3(D14Nephrotoxicity D14GradeofNephrotoxicity)

* Example generated by -dataex-. For more info, type help dataex
clear
input long MRN byte(D14HTNaftertherapy D14GradeofHTN D14Neurotoxicity D14GradeofNeurotoxicity D14Nephrotoxicity D14GradeofNephrotoxicity)
 415096 0 . 0 . 1 1
5210848 0 . 0 . 0 .
5314708 0 . 0 . 0 .
5149055 0 . 0 . 0 .
 344768 1 2 0 . 1 2
5204691 0 . 0 . 1 2
5120925 0 . 0 . 0 .
5267112 0 . 0 . 0 .
5332669 0 . 0 . 0 .
5238371 0 . 0 . 0 .
5305436 0 . 0 . 0 .
5269228 0 . 0 . 0 .
5202501 0 . 0 . 0 .
5255454 0 . 0 . 1 2
5255564 0 . 0 . 0 .
5162872 0 . 0 . 0 .
5266839 0 . 0 . 0 .
5160920 0 . 0 . 1 2
5257025 0 . 0 . 0 .
5292514 0 . 0 . 0 .
5204502 0 . 0 . 0 .
5128875 0 . 0 . 0 .
5223056 0 . 0 . 1 2
5235292 0 . 0 . 0 .
5196750 0 . 0 . 0 .
5260766 0 . 0 . 0 .
5062534 0 . 0 . 0 .
5333130 0 . 0 . 1 2
5243284 0 . 0 . 0 .
5185564 0 . 0 . 0 .
5332298 0 . 0 . 0 .
5201607 0 . 0 . 1 1
5232736 0 . 0 . 1 1
5335624 0 . 0 . 0 .
5300620 0 . 0 . 0 .
5277170 0 . 0 . 0 .
5253809 0 . 0 . 1 2
5262138 0 . 0 . 1 1
5255425 0 . 0 . 0 .
 494642 0 . 0 . 1 1
5054551 0 . 0 . 0 .
5212094 0 . 0 . 0 .
5271919 0 . 0 . 0 .
5344589 0 . 0 . 0 .
5278437 0 . 0 . 0 .
5353871 0 . 0 . 0 .
 458766 0 . 0 . 0 .
5312148 0 . 0 . 0 .
5279808 0 . 0 . 0 .
5327369 0 . 0 . 0 .
5131967 0 . 0 . 1 1
5310699 0 . 0 . 0 .
5208600 0 . 0 . 0 .
5286560 0 . 0 . 0 .
5260257 0 . 0 . 0 .
5001351 0 . 0 . 0 .
5210011 0 . 0 . 1 2
5290005 0 . 0 . 0 .
5205319 0 . 0 . 0 .
5332133 0 . 0 . 0 .
5225654 0 . 0 . 0 .
5264752 0 . 0 . 0 .
5342051 0 . 0 . 0 .
5295768 0 . 0 . 0 .
5226387 0 . 0 . 1 2
5282354 0 . 0 . 0 .
5328230 0 . 0 . 0 .
5233641 0 . 0 . 0 .
5292005 0 . 0 . 0 .
5284371 0 . 0 . 0 .
5181069 0 . 0 . 0 .
 450752 0 . 0 . 0 .
5216304 0 . 0 . 0 .
5291241 0 . 0 . 0 .
5204746 0 . 0 . 0 .
5299344 0 . 0 . 0 .
5082462 0 . 0 . 0 .
5261147 0 . 0 . 0 .
5303088 0 . 0 . 0 .
5264667 0 . 0 . 0 .
 994154 0 . 0 . 0 .
5274661 0 . 0 . 0 .
5232286 0 . 0 . 0 .
5202631 0 . 0 . 0 .
5305451 0 . 0 . 0 .
5336796 0 . 0 . 0 .
5209795 0 . 0 . 0 .
5331250 1 2 0 . 0 .
5263661 0 . 0 . 0 .
5298052 0 . 0 . 0 .
5144932 0 . 0 . 0 .
5206879 0 . 0 . 1 1
5304348 0 . 0 . 0 .
5260833 0 . 0 . 0 .
5313588 0 . 0 . 1 2
5294623 0 . 0 . 1 2
5279027 0 . 0 . 0 .
5311042 0 . 0 . 1 2
5046949 0 . 0 . 0 .
5115307 0 . 0 . 1 2
end

[Clyde Schechter]

On the assumption that the D14* variables are completely representative of all of the D#* variables:

unab vbles: D14*
local stubs: subinstr local vbles "D14" "@", all
reshape long `stubs', i(MRN) j(day) string
destring day, replace ignore("D")

[Sarah Mj]

On the assumption that the D14* variables are completely representative of all of the D#* variables:

unab vbles: D14*
local stubs: subinstr local vbles "D14" "@", all
reshape long `stubs', i(MRN) j(day) string
destring day, replace ignore("D")

There is no variable called day

[Clyde Schechter]

No. -reshape- will create a variable called day which will have values like 14. Run the code and you will see.

[Hanin Aldalham]

I want to change the data from long to wide because some records are doublicted because they use multiple treatments.

* Example generated by -dataex-. For more info, type help dataex
clear
input long subjectno float age byte(gender genomicsreport bmbatdiagnosis) int diseasestatusatinitiation float siteofrelapse byte(bmbatinitiation extramedullary)
5161057 19 2  3 2 1 1 6 1
5299035  . 1  5 6 2 1 5 1
5347383 40 2  3 6 1 2 3 2
5175144 37 1  . . 1 1 5 1
5082462 31 1  . . 1 3 . 2
5082462 31 1  . . 3 3 . 2
5207013 38 1  3 6 1 1 6 1
5207013 38 1  3 6 3 1 1 1
 892138 16 2  3 5 1 1 1 1
5201248 23 1  1 1 1 1 1 1
5118042 22 1  . 6 1 1 5 1
5265352 19 1  3 5 1 1 6 1
5168674 22 1  3 3 1 1 7 1
5168674 22 1  3 3 3 1 1 2
5337495 15 2  3 1 1 1 7 1
5166335 16 2  4 1 1 1 7 1
5166335 16 2  4 1 6 . . .
5453620 23 1  1 6 7 1 4 1
 993024 16 2  . 4 1 1 6 1
5143986 20 2  9 7 1 1 6 2
5240775 38 2  2 6 4 1 6 1
5264667 45 1  9 7 2 1 1 1
5242451 19 2  9 7 2 1 3 1
5167507 12 2 11 7 1 2 2 2
5195583 12 1  . 6 1 1 3 1
5411091 12 1  . 7 1 1 6 1
5468876 15 1  . . 1 1 6 2
5251894 19 2  9 3 1 1 1 1
5160592  5 1  1 7 1 1 7 1
5269228 15 2  9 . 2 1 4 1
5196423 13 2  4 7 1 1 2 1
5117945  7 1  9 6 1 3 1 2
5100824  5 2  9 7 1 1 6 2
5423129  4 1  9 . 2 1 7 1
5283711 11 1  9 7 1 2 1 2
5312737 16 1  9 7 2 2 1 2
5273150  6 2  . 7 1 1 3 1
5150124 16 2  . . 2 2 6 2
5322461  7 1  9 7 1 2 1 2
5102388 10 1  . . 2 2 1 2
5384932 51 2  1 2 1 1 2 1
5371134 27 1  1 1 1 1 2 1
5475302 54 1  3 7 2 2 1 2
5380376 28 2  1 6 3 1 7 2
5374242 68 2  4 1 3 1 1 1
5393746 14 2  3 4 3 1 1 1
5396156 25 1  1 6 3 1 . 1
5384867 16 2  3 7 3 1 1 1
5389831 36 1  1 4 3 1 1 1
5396101 26 1  1 6 3 1 1 1
5420282 51 2  1 6 3 1 1 1
5259766 61 2  1 6 3 1 1 .
5403071 15 1  3 4 3 1 1 1
5475302 54 1  3 7 2 2 6 2
5461254 17 1  6 6 2 1 . 1
5461254 17 1  6 6 2 1 6 1
5461254 17 1  6 6 2 1 . 1
5143313 22 1  3 7 1 1 6 1
5075735 40 2  3 . 1 1 6 1
5111090 25 1  3 7 1 1 1 1
5226597 36 2  3 7 1 1 6 1
5144260 12 1  3 6 3 1 1 1
5061029 17 2  4 6 1 1 6 1
5176475 16 1  4 6 1 2 7 1
5060633 10 2  3 2 1 . 7 1
5208348  8 2  7 6 1 1 1 1
5186986 62 1  1 6 2 1 6 1
5079220  7 1  3 7 1 1 1 1
5335291  6 2  3 7 1 1 6 1
5343862 56 1  1 7 1 1 1 1
5399619 57 1  3 6 5 1 6 1
5401570 11 2  3 7 1 1 7 1
5207637 25 1  3 . 1 2 7 2
5335053  3 1  7 3 1 1 4 1
5038859 27 2  1 6 1 1 6 1
5334666  8 1  3 6 3 1 8 1
5166335 16 2  5 1 6 1 1 1
5337495 18 2  3 5 6 1 1 1
5372887 28 2  3 2 3 1 2 1
5412319 46 1  1 6 3 1 6 1
5371134 27 1  1 . 1 1 1 1
5412413  8 2  3 4 6 1 8 1
5331660 13 1  3 7 2 1 8 1
5264835  8 1  . 6 3 1 8 1
5335293 11 2  5 1 3 1 8 1
5453620 23 1  1 6 1 1 6 1
5413158  4 1  3 . 1 1 6 1
5038859 27 2  1 6 2 1 2 1
5181412 22 1  3 6 2 1 6 1
5335293 11 2  5 1 1 1 6 1
5223056 22 2  . 6 3 1 3 .
5209166 12 1  3 8 2 1 8 1
5269467 53 1  5 6 2 1 . .
5167062 48 2  1 . 1 2 6 1
5471058 10 1  5 6 1 1 1 1
 639721 17 1  5 6 1 1 4 1
5094290 14 2  3 7 1 1 . 1
5166335 16 2  5 1 1 1 5 1
5337495 18 2  5 5 1 1 6 1
5264835  8 1  . 6 1 1 8 2
end

[Clyde Schechter]

ds subjectno gender, not
local vbles `r(varlist)'
by subjectno (age), sort: gen seq = _n
reshape wide `vbles', i(subjectno) j(seq)

will do this.

But why do you want to do it? Most Stata commands work best, or only, with long layout data. Reshaping your data to wide is advisable only if you know specifically that you are going to be using commands that require or prefer wide data, which are few in number. So think carefully before you do this--you will probably regret it. Most likely your life will be easier if you leave the data in their current long layout.

[Hanin Aldalham]

I have a problem with questions that contains multiple responses in one cell and I wanted to separate the answers by giving each number a column.

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 genomicsreport str11 diseasebiologyatdiagnosis str10 diseasebiologyatinitiation str17 electrolytesdisturbance
"3" "2,5,3,1." "5,2,1. " "9, 10"
"5" "5, 4, 6" "5" "1, 10"
"3" "5, 6,4" "5 , 6" "1,3"
"" "" "2, 5,1" "8,1,10"
"" "" "" "1,5"
"" "" "" "NA"
"3" "1,2,3,4,5,6" "5,6,2" "1"
"3" "1,2,3,4,5,6" "5,6,2" "11"
"3" "1,2" "1,2,6" "10,1,5,8"
"1" "2,3,4,6" "5,2" "1,10"
"4, 9,11" "2,5,6,3" "2,5,6,1" "10"
"3" "2,5,1,6" "2,5" "11,8"
"3" "3,2,5,1,6" "5,6" "11"
"3" "3,2,5,1,6" "5,6" "11,8"
"3" "2,5,6,3" "5,2" "8,3,10,1"
"4" "2,5" "2,3,4,1" "1"
"4" "2,5" "1" "3"
"1" "" "2,6,5" "na"
"7,11 " "5" "5" "11"
"9" "2,5,3" "2,5,1" "2"
"2" "2,5" "2,5" "9,10,1"
"9" "2,5,6,3" "" "NA"
"9" "2,5" "2,5" "11"
"11" "2,5,6" "2,5" "1,10"
"7,11" "5,2,4,3" "5,2,4,3" "8,1,10"
"5,11 " "1,2,3,5,6" "1,2,3,5,6" "3,8,5,10"
"4,11 " "1" "1" "1,10"
"9" "2,5" "2,5 " "8,5,10,1"
"1" "" "2,5,1,6" "8,10 "
"9" "" "" "1"
"4" "2,5,6" "2,5" "1,7,11"
"9" "5,2,3,6" "4,3" "1"
"9" "2,5,6" "2,5" "1,10,5,8"
"9" "5,6 " "5,6" "8"
"9" "2,5,1,6,3" "2,5,1,6" "1,3,5,10"
"9" "2,1,6,3" "2,5" "3"
"11, 4" "2,1,5,3,6" "1,2,3,4" "1"
"" "" "2,5" "2,10,3"
"9" "5,2,6,3" "5,2,6,3" "5"
"" "" "5,2" ""
"1" "" "2" "1"
"1" "1 4 5 6 " " 2 4 5 6 " "8 11 "
"3" "1 3 4 5 " "2 4" "11"
"1" "2 3" "2 3" "1"
"4" "1 2 5 6 " "1 2 5 6 " "2 8 11 "
"3" "1 5 6 " "1 2 5 6 " "1 4 11 "
"1" "1 2 5 " "1 2 5 " "1 3 8 10"
"3" "1 2 3 5 " "1 2 3 5 " "3 8 11"
"1" "1 2 3 5 6" "1 2 3 5 6" "1 3 8 11"
"1" "2 3 5 6 " "2 3 5 6 " "11"
"1" "5" "5" "1 3 5 8 10"
"1" "2 3 5 6 " "2 3 5 6 " "3 8 11"
"3" "1 3 5 6 " "1 3 5 6 " "3 5 9 11"
"3" "1 3 4 5" "1 3 4 5 " "2 4 5 11"
"6" "4 5 6 " "4 5 6 " "1 3 8 7 11 "
"6" "4 5 6 " "4 5 6 " "3 8 11"
"6" "4 5 6 " "4 5 6 " "3 11"
"3" "2 3 5 6 " "2 5 " "1 8 "
"3" "1 2 5 6 " "1 2 5 6 " "1 11 "
"3" "1" "1" "1 3 5 9 10 "
"3" "2 3 5 6 " "2 3 5 6 " "1 6 9 11 "
"3" "1 2 3 4 5 " "1 2 3 4 5 " "1 3 5 9 11 "
"4" "1 2 5 " "1 2 5 " "1 3 5 8 11"
"4" "1" "1" "1 3 5 8 10 "
"3" "2 3 5 6 " "2 3 5 6 " "1 3 5 8 11"
"7" "1 2 " "1 2 " "1 3 8 10"
"1" "2 3 5 6 " "2 3 5 6 " "1 3 5 8 11 "
"3" "2 3 5 6 " "2 3 5 6 " "2 3 7 9 11"
"3" "1 2 3 5 6" "2 5 " "1 3 5 9 11"
"1" "2 3 5 6 " "2 3 5 6 " "10"
"3" "1 2 3 4 " "1 2 3 4 " "1 3 6 9 11 "
"3" "3 4 " "3 4 " "1 3 5 9 11 "
"3" "1 2 " "1 2 " "3 5 11 "
"7" "2 3 5 6 " "4 5 " "5 9 10 "
"1" "2,3,4,5,6" "2,3,4,5,6" ""
"3" "2,3,5,6" "2,3,5,6" "11"
"5" "1,2,5" "5" "1,3,5,8,11"
"3" "2, 3, 5,6" "2, 3,5,6" "11"
"3" "2,3,5,6" "2,3,5,6" "1,3,8,11"
"1" "2,3,5" "2,3,5" "11"
"1" "1,5,6" "1,5,6" "1,3,8,11"
"3" "1,2,5,6" "5" ""
"3" "" "" "11"
"" "" "" "3,5,11"
"5" "2,5,6" "2,5,6" "1,3,11"
"1" "" "" ""
"3" "2,5,6" "2,5" "1,3,5,7,8,10"
"1" "2,3,4,5,6" "2,3,4,5,6" ""
"3" "3" "3" "1,3,5,9,10"
"5" "2,5,6" "2,6" "1,3,5,8,11"
"1,6" "1,2,5,6" "1,5" "1,3,8,10"
"3" "2,3,4,5" "2,3,4,5" "3,5,7,8,9"
"5" "2,3,5,6" "5" "1,3,5,8,10"
"1" "2, 3, 5,6" "2,5" "7"
"5" "1,2" "1,2" "1,3,5,8,11"
"5" "2,3,5,6" "2,3,5,6" "3,5,8,10"
"3" "1,2,3,6" "1,2,3,6" "1, 3, 5,10"
"5" "1,2,5" "5" "1,8,10"
"5" "2, 3 , 5,6" "2,5,6" "8"
"" "" "" "1,3,5,7,8,10"
end
[/CODE]

[Clyde Schechter]

-help split-

[Fatimah Gh]

I tried to change the data structure using reshape from this:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte serialno str7 department str2 units str34 outcomes byte outcomecode float(q1_19 q2_19) byte q3_19 float q4_19
1 "Nursing" "A4" "Number of respondents"              1   52   49 43   53
2 "Nursing" "A4" "Satisfaction Mean Score"            2 91.6 90.1 89 87.5
3 "Nursing" "A4" "Number of Nurses "                  3   32   34 34   35
4 "Nursing" "A4" "Nationalities"                      4    7    8  8    7
5 "Nursing" "A4" "Average number of Years of Service" 5    8    7  7    7
end

to this:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte serialno str7 department str2 units int year byte(qurtile numberofrespondents) float satisfactionmeanscore byte(numberofnurses nationalities averagenumberofyearsofservice)
1 "Nursing" "A4" 2019 1 52 91.6 32 7 8
2 "Nursing" "A4" 2019 2 49 90.1 34 8 7
3 "Nursing" "A4" 2019 3 43   89 34 8 7
4 "Nursing" "A4" 2019 4 53 87.5 35 7 7
end

[Clyde Schechter]

As stated, your request is impossible. Your desired output includes variables like qurtile and year that do not even exist in the input, and there is no indication of how their values might be created.

However, it looks like the gist of what you want to accomplish is to transpose the part of the outcome found in variables q*, so that the "outcomes" become the variables and the different q* entities, which I think of as items in a test or survey, become separate observations. This can be done as follows:

rename q* _q*
reshape long _, i(serialno) j(item) string

replace outcomes = substr(strtoname(lower(subinstr(outcomes, " ", "", .))), 1, 31)
drop outcomecode serialno

reshape wide _, i(item) j(outcomes) string
rename _* *

Perhaps you can get to where you want from here with other information in your data set not shared in your post.

[Hemanshu Kumar]

I think Fatima's intention is for the variable names q?_xx to provide the qurtile (presumably you mean quarter?) and year components. With that assumption, all you need is a small addition to Clyde's code:

rename q* _q*
reshape long _, i(serialno) j(item) string

replace outcomes = substr(strtoname(lower(subinstr(outcomes, " ", "", .))), 1, 31)
drop outcomecode serialno

reshape wide _, i(item) j(outcomes) string
rename _* *

gen byte qurtile = real(substr(item, 2, 1))
gen int year = 2000 + real(substr(item, -2, .))
drop item

order department units year qurtile

[Fatimah Gh]

Thank you all, and yes like what Hemanshu Kumar said.

However, this only worked for the part of the dataset that I shared. When I tried it on the full dataset, I encountered an error:
Values of variable outcomes are not unique within item.
Your data are currently long.

Is there anything you can suggest helping resolve this issue?
Since I have more than one department, year and unit.

[Hemanshu Kumar]

Try changing the second reshape command to this:

reshape wide _, i(department units item) j(outcomes) string