String multiple observations per ID according to a second variable

[Palina Shauchuk]

I m trying to reshape my data where I am having a problem with multiple observations per ID according to a second variable.
the idea is to have one id with name and sector variations like in wide format.
Data example is uploaded in attachment (Sheet1-original data).
there are almost 70000 of observations in my original big dataset.
Stata outcome results:
1. reshape wide name, i(id) j(sector) string
(note: j = COMPANY UNIVERSITY UNIVERSITY COLLEGE UNIVERSITY RESEARCH CENTRE UNIVERSITY_FOR)
dup not constant within id
Type "reshape error" for a listing of the problem observations.

I found also here topic "Multiple observations per ID according to a second variable" - http://www.statalist.org/forums/foru...econd-variable, but its not working for me as i dont have numbers.
Could you please help me to solve this problem.
I attached the example of how it should look for me (attachment: Sheet2- wanted change).

thank you for help

[Marcos Almeida]

Hello Palina,

Welcome to the Statalist / Stata Forum.

Please read the FAQ, particularly on how to share command and data.

I can hardly understand what you wish. What is more, shall I wish to try to provide an answer, I'd have to type for a long time.

On the hand, if you act according to the FAQ, you would provide a reliable chunk of data, and that is what is needed. Hence, not much.

For the forthcoming messaged, instead of saying it is "not working" , I kindly underline that the best strategy is providing command and output.

Thanks.

[William Lisowski]

Reading the Statalist FAQ linked to from the top of the page as Carlo recommended, would tell you why providing Excel spreadsheets is not recommended. Below I have read your sample data into Stata and present the first 100 observations of the original data (and a picture of the full sample output) you provided, using the dataex command as the FAQ recommends.

With that said, what you request is almost certainly not what you need for analysis in Stata. The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data.

Perhaps someone else will be willing to show you the directions down the wrong path. I cannot.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id str14 sector str32 name
     1 "COMPANY"        "CARADON STELRAD"                
     1 "COMPANY"        "HYDRO ALUMINIUM"                
     1 "COMPANY"        "HYDRO ALUMINIUM"                
     1 "COMPANY"        "HYDRO ALUMINIUM"                
     1 "COMPANY"        "HYDRO ALUMINIUM"                
     2 "UNIVERSITY"     "UNIVERSITE CATHOLIQUE DE LOUVAIN"
     2 "UNIVERSITY"     "KATHOLIEKE UNIVERSITEIT LEUVEN"  
     2 "UNIVERSITY"     "KATHOLIEKE UNIVERSITEIT LEUVEN"  
     3 "COMPANY"        "ENDOCONTROL"                    
     3 "UNIVERSITY_FOR" "UNIVERSITE PIERRE & MAIRE CURIE"
     3 "UNIVERSITY_FOR" "UNIVERSITE PIERRE & MAIRE CURIE"
207995 "COMPANY"        "Umicore"                        
207995 "COMPANY"        "Umicore"                        
207995 "COMPANY"        "Umicore"                        
207995 "COMPANY"        "Umicore"                        
207995 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208023 "COMPANY"        "ALCATEL"                        
208023 "COMPANY"        "ALCATEL"                        
208023 "COMPANY"        "ALCATEL"                        
208023 "COMPANY"        "ALCATEL"                        
208034 "COMPANY"        "ALCATEL"                        
208034 "COMPANY"        "ALCATEL"                        
208059 "UNIVERSITY"     "KATHOLIEKE UNIVERSITEIT LEUVEN"  
208059 "UNIVERSITY"     "UNIVERSITE CATHOLIQUE DE LOUVAIN"
208059 "UNIVERSITY"     "KATHOLIEKE UNIVERSITEIT LEUVEN"  
208059 "UNIVERSITY"     "KATHOLIEKE UNIVERSITEIT LEUVEN"  
208063 "COMPANY"        "BARCO"                          
208063 "COMPANY"        "BARCO"                          
208063 "COMPANY"        "BARCO"                          
208289 "COMPANY"        "ADVANCED CELL TECH"              
208289 "COMPANY"        "ADVANCED CELL TECH"              
208289 "COMPANY"        "ADVANCED CELL TECH"              
208364 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208364 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208364 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208380 "COMPANY"        "PRIMUS"                          
208380 "COMPANY"        "PRIMUS"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208383 "COMPANY"        "BARCO"                          
208385 "COMPANY"        "PRIMUS"                          
208385 "COMPANY"        "PRIMUS"                          
208386 "COMPANY"        "PRIMUS"                          
208386 "COMPANY"        "PRIMUS"                          
208386 "COMPANY"        "PRIMUS"                          
208387 "COMPANY"        "STMicroelectronics"              
208387 "COMPANY"        "STMicroelectronics"              
208387 "COMPANY"        "STMicroelectronics"              
208403 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208403 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208403 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208403 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208404 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208404 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208410 "COMPANY"        "STMicroelectronics"              
208410 "COMPANY"        "STMicroelectronics"              
208410 "COMPANY"        "STMicroelectronics"              
208410 "COMPANY"        "STMicroelectronics"              
208410 "COMPANY"        "STMicroelectronics"              
208411 "COMPANY"        "STMicroelectronics"              
208411 "COMPANY"        "STMicroelectronics"              
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208412 "COMPANY"        "ON SEMICONDUCTOR BELGIUM"        
208413 "COMPANY"        "SEMICONDUCTOR COMPONENTS"        
208413 "COMPANY"        "SEMICONDUCTOR COMPONENTS"        
208413 "COMPANY"        "SEMICONDUCTOR COMPONENTS"        
208414 "COMPANY"        "STMicroelectronics"              
208414 "COMPANY"        "STMicroelectronics"              
208414 "COMPANY"        "STMicroelectronics"              
208414 "COMPANY"        "STMicroelectronics"              
208426 "COMPANY"        "PRIMUS"                          
208426 "COMPANY"        "PRIMUS"                          
208426 "COMPANY"        "PRIMUS"                          
208427 "COMPANY"        "SADEF"                          
208427 "COMPANY"        "SADEF"                          
208427 "COMPANY"        "SADEF"                          
208428 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208428 "UNIVERSITY"     "UNIVERSITEIT GENT"              
208431 "COMPANY"        "VDM LASER OPTICS"                
208431 "COMPANY"        "VDM LASER OPTICS"                
208431 "COMPANY"        "VDM LASER OPTICS"                
208435 "COMPANY"        "VWV"                            
208435 "COMPANY"        "VWV"                            
208436 "COMPANY"        "BARCO"                          
208436 "COMPANY"        "BARCO"                          
208436 "COMPANY"        "BARCO"                          
208437 "COMPANY"        "BARCO"                          
end

Here is a picture of the spreadsheet containing the desired output.

[Palina Shauchuk]

Dear William,
i had a thought about your version and i think it ll be more useful than my previous one. Thank you a lot for help.