Hello, I have a pretty simple data file that shows and ID variable and a integer variable for number of sales. The report has them rolled up with subtotals,


Code:
* Example generated by -dataex-.    To    install:    ssc    install    dataex
clear
input str12 ID long SalesCount
"006000"          .
"2018"            .
"May"           514
"June"          624
"July"          695
"August"        736
"September"     505
"October"       264
"November"      286
"December"      299
"Total 2018"   3923
"2019"            .
"January"       227
"February"      478
"March"         991
"April"        1362
"Total 2019"   3058
"Total 006000" 6981
"006100"          .
"2018"            .
"September"     229
"October"       359
"November"      310
"December"      238
"Total 2018"   1136
"2019"            .
"January"       282
"February"      132
"March"         103
"April"          57
"Total 2019"    574
"Total 006100" 1710
"Grand Total:" 8691
end
In this portion of the dataset, there are two IDs, ("006000", "006100") for which there is a total by month. What I am trying to do is create a dateid variable that contains "tm%" formatted year-month for each observation and to create group ID = ID of the group. The subtotals and grand Totals are from the report and are not important, but I left those obs here in case they can facilitate getting the groups defined the way I need. I appreciate any help on the approach to use.