Hi Statalisters,

Could someone help me with the below or direct me to any previous thread (which I could not identify) indicating a solution?

I am trying to retrieve the name and frequency of N observations under 3 variables (presented in columns), where by frequencies are calculated across rows of the 3 variables. For example, the data below gives information on 3 tests conducted on 15 participants to identify a bacteria from different type of specimens to diagnose a particular disease. "SpecimenTest1" lists the specimens used to conduct the first test from the 15 participants. The results of first test could come as positive or negative. If the bacteria was not identified in first test for patients whose clinical symptoms indicted presence of disease/possibility of infection, the doctor advised a second test on same or other specimens ("SpecimenTest2") collected from such participants (e.g., participant Nos. 4, 8 and 11). If the second test also failed, a 3rd test with specimens ("SpecimenTest3") available was conducted to be extra sure (e.g., participant nos. 4 and 8) . From the data given below, I want to get the names and frequencies of total samples tested for these 15 participants across test 1, test 2 and test 3. In the given example, my final output table should look something like:.
Type of Specimen Freq
Blood 7
Bone 4
Gastric 1
Lymph 2
Pus 4
Urine 2
Total 20










I got the frequencies (= 20) by running the below code. However I also want the names of the type of specimens (as given under Type of Specimen column in the above table) as I have to do further analysis stratified by this "Type of Specimen" variable.

Code:
gen SpecimenTest1_n=0
replace SpecimenTest1_n=1 if SpecimenTest1!="NA"

gen SpecimenTest2_n=0 
replace SpecimenTest2_n=1 if SpecimenTest2!="NA" 

gen SpecimenTest3_n=0 
replace SpecimenTest3_n=1 if SpecimenTest3!="NA" 

gen TypeofSpecimen_123n=SpecimenTest1_n+SpecimenTest2_n+SpecimenTest3_n
sum TypeofSpecimen_123n
display r(sum)
Given below is the sample data set produced by -dataex-. I use Stata/SE 13 on Windows 10

Thankyou

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long CaseID float Age str2 Sex str24(SpecimenTest1 SpecimenTest2 SpecimenTest3)
 1 10 "F" "Bone"    "NA"    "NA"  
 2  5 "M" "Pus"     "NA"    "NA"  
 3  3 "M" "Blood"   "NA"    "NA"  
 4 10 "F" "Blood"   "Bone"  "Bone"
 5  8 "M" "Blood"   "NA"    "NA"  
 6  8 "M" "Lymph"   "NA"    "NA"  
 7  9 "F" "Bone"    "NA"    "NA"  
 8 11 "F" "Blood"   "Lymph" "Pus" 
 9  9 "F" "Urine"   "NA"    "NA"  
10  7 "F" "Pus"     "NA"    "NA"  
11  2 "M" "Blood"   "Blood" "NA"  
12  6 "M" "Gastric" "NA"    "NA"  
13 14 "F" "Pus"     "NA"    "NA"  
14  9 "M" "Blood"   "NA"    "NA"  
15  5 "M" "Urine"   "NA"    "NA"  
end