How to tag individual student group by sex in vertical order

[Bernie Seville]

Dear Statalist,

Across multiple directories and subfolders, I have multiple Excel files that show a group of male and female students arranged vertically.
By generating the "sex" variable, I would like to know how to tag each student based on their unique sex identity. My apology if my question is not very clear.
Newbie here and I really don't know how to start my code.

Here's my data sample:

clear
input str32 A str32 B str32 C str32(D) str4(E) str6 F str32(G)
"" "FORM S5.2" "" "" "" ""
"" "" "" "" "" ""
"" "" "" "" "" ""
"" "School ID" "123456" ""
"" "School Name" "Orchid School"
"" "" "" "" "" "" ""
"No." "LRN" "NAME" "Age" "Grade" "Pass" "ResID"
"" "" "" "" "" "" ""
"" "" "MALE" "" "" "" ""
"1" "1234" "Josh" "4" "5" "PF" "7"
"2" "2235" "Robert" "7" "7" "NF" "7"
"3" "3236" "Arnel" "6" "6" "NF" "6"
"4" "1237" "Brandan" "4" "3" "PF" "4"
"5" "7239" "Royal" ".." ".." ".." ".."
"6" "1243" "Brazil" ".." ".." ".." ".."
"7" "1246" "Bob" "6" "3" "PF" "2"
"" "7" "<=====TOTAL MALE" "" "" ""
"" "" "FEMALE" "" "" "" ""
"1" "7891" "Olivia" "4" "5" "PF" "7"
"2" "7892" "Emma" "7" "7" "NF" "7"
"3" "7893" "Amelita" "6" "6" "NF" "6"
"4" "8971" "Sophia" "4" "3" "PF" "4"
"5" "8973" "Isabella" ".." ".." ".." ".."
"6" "3456" "Rhodora" ".." ".." ".." ".."
"7" "3457" "Lucy" "6" "3" "PF" "2"
"" "7" "<=====TOTAL FEMALE" "" "" "" ""
end

Appreciated any help.

[Bernie Seville]

Statalist,

I apologize if my question was unclear. This is the outcome I wanted (H column).

clear
input str32 A str32 B str32 C str32(D) str4(E) str6 F str32(G) str6 H
"" "FORM S5.2" "" "" "" ""
"" "" "" "" "" ""
"" "" "" "" "" ""
"" "School ID" "123456" ""
"" "School Name" "Orchid School"
"" "" "" "" "" "" ""
"No." "LRN" "NAME" "Age" "Grade" "Pass" "ResID" "Sex"
"" "" "" "" "" "" "" ""
"" "" "MALE" "" "" "" "" ""
"1" "1234" "Josh" "4" "5" "PF" "7" "Male"
"2" "2235" "Robert" "7" "7" "NF" "7" "Male"
"3" "3236" "Arnel" "6" "6" "NF" "6" "Male"
"4" "1237" "Brandan" "4" "3" "PF" "4" "Male"
"5" "7239" "Royal" ".." ".." ".." ".." "Male"
"6" "1243" "Brazil" ".." ".." ".." ".." "Male"
"7" "1246" "Bob" "6" "3" "PF" "2" "Male"
"" "7" "<=====TOTAL MALE" "" "" "" ""
"" "" "FEMALE" "" "" "" "" ""
"1" "7891" "Olivia" "4" "5" "PF" "7" "Female"
"2" "7892" "Emma" "7" "7" "NF" "7" "Female"
"3" "7893" "Amelita" "6" "6" "NF" "6" "Female"
"4" "8971" "Sophia" "4" "3" "PF" "4" "Female"
"5" "8973" "Isabella" ".." ".." ".." ".." "Female"
"6" "3456" "Rhodora" ".." ".." ".." ".." "Female"
"7" "3457" "Lucy" "6" "3" "PF" "2" "Female"
"" "7" "<=====TOTAL FEMALE" "" "" "" ""
end

[Nick Cox]

Thanks for the clear data example. There is one over-arching problem here: Stata is not a spreadsheet. So, what works well -- in some senses is ideal -- in spreadseet management, that a worksheet can contain data, metadata and results of calculation, won't work well in Stata.

I suspect that this you know this very well, but my example output goes much further than you ask, as your desired example still includes metadata.

You could, perhaps should, have an extra variable holding the form name.

clear
input str32 A str32 B str32 C str32(D) str4(E) str6 F str32(G)
"" "FORM S5.2" "" "" "" ""
"" "" "" "" "" ""
"" "" "" "" "" ""
"" "School ID" "123456" ""
"" "School Name" "Orchid School"
"" "" "" "" "" "" ""
"No." "LRN" "NAME" "Age" "Grade" "Pass" "ResID"
"" "" "" "" "" "" ""
"" "" "MALE" "" "" "" ""
"1" "1234" "Josh" "4" "5" "PF" "7"
"2" "2235" "Robert" "7" "7" "NF" "7"
"3" "3236" "Arnel" "6" "6" "NF" "6"
"4" "1237" "Brandan" "4" "3" "PF" "4"
"5" "7239" "Royal" ".." ".." ".." ".."
"6" "1243" "Brazil" ".." ".." ".." ".."
"7" "1246" "Bob" "6" "3" "PF" "2"
"" "7" "<=====TOTAL MALE" "" "" ""
"" "" "FEMALE" "" "" "" ""
"1" "7891" "Olivia" "4" "5" "PF" "7"
"2" "7892" "Emma" "7" "7" "NF" "7"
"3" "7893" "Amelita" "6" "6" "NF" "6"
"4" "8971" "Sophia" "4" "3" "PF" "4"
"5" "8973" "Isabella" ".." ".." ".." ".."
"6" "3456" "Rhodora" ".." ".." ".." ".."
"7" "3457" "Lucy" "6" "3" "PF" "2"
"" "7" "<=====TOTAL FEMALE" "" "" "" ""
end

gen morf = lower(C) if inlist(C, "MALE", "FEMALE") 
replace morf = morf[_n-1] if missing(morf)

gen school_id = C if B == "School ID"
replace school_id = school_id[_n-1] if missing(school_id)
gen school_name = C if B == "School Name"
replace school_name = school_name[_n-1] if missing(school_name)

foreach v of var A-G { 
    local new = strtoname(`v'[7])
    rename `v' `new' 
}

drop if missing(NAME) | inlist(NAME, "MALE", "FEMALE") | substr(NAME, 1, 1) == "<"
drop if missing(real(LRN))

list 



     +---------------------------------------------------------------------------------------+
     | No_    LRN       NAME   Age   Grad   Pass   ResID     morf   school~d     school_name |
     |---------------------------------------------------------------------------------------|
  1. |   1   1234       Josh     4      5     PF       7     male     123456   Orchid School |
  2. |   2   2235     Robert     7      7     NF       7     male     123456   Orchid School |
  3. |   3   3236      Arnel     6      6     NF       6     male     123456   Orchid School |
  4. |   4   1237    Brandan     4      3     PF       4     male     123456   Orchid School |
  5. |   5   7239      Royal    ..     ..     ..      ..     male     123456   Orchid School |
     |---------------------------------------------------------------------------------------|
  6. |   6   1243     Brazil    ..     ..     ..      ..     male     123456   Orchid School |
  7. |   7   1246        Bob     6      3     PF       2     male     123456   Orchid School |
  8. |   1   7891     Olivia     4      5     PF       7   female     123456   Orchid School |
  9. |   2   7892       Emma     7      7     NF       7   female     123456   Orchid School |
 10. |   3   7893    Amelita     6      6     NF       6   female     123456   Orchid School |
     |---------------------------------------------------------------------------------------|
 11. |   4   8971     Sophia     4      3     PF       4   female     123456   Orchid School |
 12. |   5   8973   Isabella    ..     ..     ..      ..   female     123456   Orchid School |
 13. |   6   3456    Rhodora    ..     ..     ..      ..   female     123456   Orchid School |
 14. |   7   3457       Lucy     6      3     PF       2   female     123456   Orchid School |
     +---------------------------------------------------------------------------------------+

[Bernie Seville]

Hi Nick,

Thank you very much. You save my life today. This is all I need.