Combining multiple categorical variables to one categorical variables

[Subash Adhikari]

Hi

I would like to combine multiple categorical variables to one in stata. I went through various posts, tutorials and manuals but could not fond what i want to do, so I am here.
To elaborate, I have observations in the row and variables in the column as below.
I would like stata to recognize 'PRO-SEC' as one variable with content below it. However when I import this data to stata, it generates seperate column for the merged column of 'PRO-SEC' and assigns variable to each columns.
When I encode these string variables as categorical, and apply command, generate sectot= prosec+var10+var11+var12 stata just adds up the numeric value assigned to each categorical variables and sums it up.

How can I have a variable prosec with all the contents in it. (The image i want is shown in the excel)
The question may arise why dont i import it after consolidating it it excel, but the values are too numerous to manually consolidate in excel.

<In excel>

No.	PRO-SEC
685	VAS
687	VAS	HRQOL	SF-36	RLS-QOL
688	VAS
689	DLQI	VAS	HAQscore
693	DLQI	HAQscore	SF-36

<In stata>

prosec	var10	var11	var12
VAS
VAS	HRQOL	SF-36	RLS-QOL
VAS
DLQI	VAS	HAQscore
DLQI	HAQscore	SF-36

Image of desired outcome

prosec

VAS

VAS

VAS

DLQI

DLQI

HRQOL

VAS

HAQscore

SF-36

HAQscore

SF-36

RLS-QOL

Sorry it seems that the table doesnot appear as table when it is posted.

Many thanks

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 prosec str8(var10 var11) str7 var12
"VAS"  ""         ""         ""      
"VAS"  "HRQOL"    "SF-36"    "RLS-QOL"
"VAS"  ""         ""         ""      
"DLQI" "VAS"      "HAQscore" ""      
"DLQI" "HAQscore" "SF-36"    ""      
end

rename (*) (prosec#), addnumber(1)
gen variable=_n
reshape long prosec, i(variable) j(which)
drop if missing(prosec)

Res.:

. sort which variable

. l, sep(0)

     +-----------------------------+
     | variable   which     prosec |
     |-----------------------------|
  1. |        1       1        VAS |
  2. |        2       1        VAS |
  3. |        3       1        VAS |
  4. |        4       1       DLQI |
  5. |        5       1       DLQI |
  6. |        2       2      HRQOL |
  7. |        4       2        VAS |
  8. |        5       2   HAQscore |
  9. |        2       3      SF-36 |
 10. |        4       3   HAQscore |
 11. |        5       3      SF-36 |
 12. |        2       4    RLS-QOL |
     +-----------------------------+

.