Hi folks. I have data on public beach visitation by zipcode. For each zip, I have a list of sites where residents from that zip were observed and interviewed. The data is in the following format:

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long zip byte(n_sites n_choicesets) str5 sites_visited
11223 1 2 "D"    
60660 3 4 "H J A"
48912 1 2 "C"    
66206 2 3 "N C " 
end
Each observation represents a zip code, n_sites denotes the number of sites at which people from that zip code were interviewed, n_choicesets is equal to n_sites+1, and sites_visited is a list of alphabetical identifiers corresponding to the distinct sites where people from that zip code were interviewed. For example, in the above data, people from zip 60660 were observed at sites H, J, and A.

I'd like to transform the data to this structure:

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long zip byte(n_sites n_choicesets) str7 sites_visited
11223 1 2 "D"      
11223 1 2 "No trip"
60660 3 4 "H"      
60660 3 4 "J"      
60660 3 4 "A"      
60660 3 4 "No trip"
48912 1 2 "C"      
48912 1 2 "No trip"
66206 2 3 "N"      
66206 2 3 "C"      
66206 2 3 "No trip"
end
so each zip code now is expanded by n_choicesets, and each zip code observation is matched with one of the sites from the original sites_visited list. The last observation for each zip code takes on the value "No trip" in the sites_visited column. I've done this before a while back, and I can't for the life of me remember how. Any help would be appreciated! thank you.