Calculate percentages across rows

[Beth Unger CCCC]

Below is an example dataset. I want to create multiple columns representing the percentage calculated by taking the columns that end in a number (e.g. newTotalCo1) and dividing them by newTotalCLOTotal. That way, if I add up the percentages ACROSS the rows, it equals 100%. After doing that, I will drop the count numbers and reshape the percentage data into a long format.

For example - newTotalCo1/newTotalCLOTotal newTotalCo2/newTotalCLOTotal

Competency CoPercent1 CoPercent 2 CoPercent3
1 0 .105 .263
2 .143 0 .238

* Example generated by -dataex-. For more info, type help dataex
clear
input byte Competency float(newTotalCLOTotal newTotalCo1 newTotalCo2 newTotalCo3 newTotalCo4 newTotalCo5 newTotalCo6 newTotalCo7 newTotalCo8 newTotalCo9)
1 19 0 2 5 3 4 0 4 1 0
2 21 3 0 5 1 3 0 4 3 2
3 15 5 3 0 1 1 1 2 2 0
4 11 2 1 1 0 2 0 1 3 1
5  5 4 0 1 0 0 0 0 0 0
6  3 0 0 2 0 0 0 0 0 1
7 16 2 5 1 1 0 0 0 4 3
8 13 1 3 1 1 1 2 3 0 1
9 12 1 5 0 0 0 3 1 2 0
end
label values Competency CompetencyLabel
label def CompetencyLabel 1 "Communication and Advise", modify
label def CompetencyLabel 2 "Data Analysis and Synthesis", modify
label def CompetencyLabel 3 "Information Management", modify
label def CompetencyLabel 4 "Interacting with Computers", modify
label def CompetencyLabel 5 "Organize, Plan, and Prioritize", modify
label def CompetencyLabel 6 "Prepare Specimens, Tools, or Equipment", modify
label def CompetencyLabel 7 "Process Control", modify
label def CompetencyLabel 8 "Quality Control", modify
label def CompetencyLabel 9 "Troubleshooting and Maintenance", modify

Thank you!

[Clyde Schechter]

Well, since you are going to -reshape- the resulting percentages into a long layout anyway, why not do things the simple way and -reshape long- first?

reshape long newTotalCo, i(Competency) j(_j)
rename newTotalCo CoPercent
replace CoPercent = 100*CoPercent/newTotalCLOTotal

[Beth Unger CCCC]

Thank you, Clyde! Worked like a charm! Have a wonderful day. Beth