Reshaping longer question

[Maroun Mezher]

Hello,

I have the following dataset (table 1) that I am trying to pivot longer to make it look like table 2 below. However, when I run my code below it returns an error message. Could someone please help me with this? I tried using the documentation but I could not understand the syntax in there. Thanks!

Table 1:

club_id	club_name	ft_coaches	ft_scouts	ft_physiotherapists	ft_assistants
"1056"	"Eagles FC"	3	5	3	7
"1078"	"Bears FC"	7	10	5	12

Table 2:

club_id	club_name	role	count
"1056"	"Eagles FC"	ft_coaches	3
"1056"	"Eagles FC"	ft_scouts	5
"1056"	"Eagles FC"	ft_physiotherapists	3
"1056"	"Eagles FC"	ft_assistants	7
"1078"	"Bears FC"	ft_coaches	7
"1078"	"Bears FC"	ft_scouts	10
"1078"	"Bears FC"	ft_physiotherapists	5
"1078"	"Bears FC"	ft_assitants	12

 reshape long ft_coaches ft_scouts ft_physiotherapists ft_assistants, i(club_name) j(role)

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 club_id str11 club_name byte(ft_coaches ft_scouts ft_physiotherapists ft_assistants)
`""1056""' `""Eagles FC""' 3  5 3  7
`""1078""' `""Bears FC""'  7 10 5 12
end

rename ft* countft*
reshape long count, i(club*) j(role) string

Res.:

. l, sep(0)

     +-----------------------------------------------------+
     | club_id     club_name                  role   count |
     |-----------------------------------------------------|
  1. |  "1056"   "Eagles FC"         ft_assistants       7 |
  2. |  "1056"   "Eagles FC"            ft_coaches       3 |
  3. |  "1056"   "Eagles FC"   ft_physiotherapists       3 |
  4. |  "1056"   "Eagles FC"             ft_scouts       5 |
  5. |  "1078"    "Bears FC"         ft_assistants      12 |
  6. |  "1078"    "Bears FC"            ft_coaches       7 |
  7. |  "1078"    "Bears FC"   ft_physiotherapists       5 |
  8. |  "1078"    "Bears FC"             ft_scouts      10 |
     +-----------------------------------------------------+

[Maroun Mezher]

this works, thanks!