Create a table with an arbitary number of columns/variables

[Daniel Lee]

Hi,

I have a dataset with some observations e.g. number of days a patient had the influenca and different symptoms (headache, fever, feeling sick, etc.). Now, I want to create a table that has the number of participants with each symptom on each day.
For each symptom I created a counting variable that counts the number of patients suffering from each symptom. Now, I want to but the data into a table. Right now I am using the tabdisp-command, i.e.

tabdisp Day_of_Influenca, cell(symp1 symp2 symp3 symp4 symp5).

Unfortunately, tabdisp only allows for five variables to be included within the cell and the data includes more than 5 symptoms.

Does anyone know how to deal with this kind of problem?
I'm relatively new to stata, so any help would be greatly appreciated.

Kind regards
Daniel

[Nick Cox]

Please post a data example. It can be simplified and the data can be fake (might need to be fake) but we just need something similar in spirit and structure to your real data.

[Daniel Lee]

I have created a data example as an Excelfile. I hope it will help.
example_data.xlsx

[Nick Cox]

Please see https://www.statalist.org/forums/help#stata for why Excel attachments are deprecated here.

[Daniel Lee]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input int ID byte(DayofInfluenca symp1) str8 symp2 str9(symp3 symp4 symp5) str8(symp6 symp7)
1530  7 . ""         "very weak" "very weak" "weak"      ""         "moderate"
3550  6 . ""         ""          ""          "moderate"  "moderate" ""        
 400 13 . ""         ""          ""          ""          ""         ""        
 360  3 . "moderate" "moderate"  ""          "moderate"  ""         ""        
1190  5 . ""         ""          ""          ""          ""         ""        
3220  2 . ""         "moderate"  ""          ""          "moderate" ""        
 330  9 . ""         ""          ""          ""          ""         ""        
2890 10 . ""         ""          ""          "very weak" "moderate" ""        
2780  2 . ""         ""          ""          ""          ""         ""        
  60  1 . "strong"   "moderate"  ""          "moderate"  ""         "weak"    
1340  6 . ""         ""          "weak"      "weak"      ""         ""        
3660  9 . ""         "very weak" "very weak" ""          ""         ""        
3380  7 . ""         ""          ""          ""          ""         ""        
3850  9 . ""         ""          ""          ""          ""         "strong"  
4610  5 . ""         "very weak" ""          "weak"      ""         "moderate"
 590 10 . ""         ""          ""          ""          ""         ""        
4040  2 . ""         ""          ""          "strong"    ""         ""        
end

------------------ copy up to and including the previous line ------------------

example_data.dta

[Nick Cox]

Thanks for the data example. I don't understand all the variables, but this may help:

* Example generated by -dataex-. For more info, type help dataex
clear
input int ID byte(DayofInfluenca symp1) str8 symp2 str9(symp3 symp4 symp5) str8(symp6 symp7)
1530  7 . ""         "very weak" "very weak" "weak"      ""         "moderate"
3550  6 . ""         ""          ""          "moderate"  "moderate" ""        
 400 13 . ""         ""          ""          ""          ""         ""        
 360  3 . "moderate" "moderate"  ""          "moderate"  ""         ""        
1190  5 . ""         ""          ""          ""          ""         ""        
3220  2 . ""         "moderate"  ""          ""          "moderate" ""        
 330  9 . ""         ""          ""          ""          ""         ""        
2890 10 . ""         ""          ""          "very weak" "moderate" ""        
2780  2 . ""         ""          ""          ""          ""         ""        
  60  1 . "strong"   "moderate"  ""          "moderate"  ""         "weak"    
1340  6 . ""         ""          "weak"      "weak"      ""         ""        
3660  9 . ""         "very weak" "very weak" ""          ""         ""        
3380  7 . ""         ""          ""          ""          ""         ""        
3850  9 . ""         ""          ""          ""          ""         "strong"  
4610  5 . ""         "very weak" ""          "weak"      ""         "moderate"
 590 10 . ""         ""          ""          ""          ""         ""        
4040  2 . ""         ""          ""          "strong"    ""         ""        
end

drop symp1 

reshape long symp, i(ID) j(day)

label def order 1 strong 2 moderate 3 weak 4 "very weak"
encode symp, gen(symptoms) label(order)

tab day symptoms 


           |                  symptoms
       day |    strong   moderate       weak  very weak |     Total
-----------+--------------------------------------------+----------
         2 |         1          1          0          0 |         2 
         3 |         0          3          0          3 |         6 
         4 |         0          0          1          2 |         3 
         5 |         1          3          3          1 |         8 
         6 |         0          3          0          0 |         3 
         7 |         1          2          1          0 |         4 
-----------+--------------------------------------------+----------
     Total |         3         12          5          6 |        26

[Daniel Lee]

Thank you, but it still is not quite what I want it to be like. I was thinking more about a table like this:

[Nick Cox]

symp1 is all missing in your example data and not even string. But it would be shown if it existed similarly to your other variables.

What are the frequencies in your desired table? Any symptom being mentioned?

[Daniel Lee]

The frequencies in the table are simply the total amount of people experiencing the symptoms at that day.

[Nick Cox]

So whatever is not missing in any variable and observation?

[Daniel Lee]

Could you perhaps phrase out last question differently? I do not quite get the question.
Concerning the wanted output: what I want is a table that for each day and symptom shows the amount of patients that suffer from that symptom that day.

[Daniel Lee]

Could you perhaps phrase the last question differently? I do not quite get the question.
Concerning the wanted output: what I want is a table that for each day and symptom shows the amount of patients that suffer from that symptom that day.

[Nick Cox]

Not easily. With a string variablean empty string "" means missing and anything else is not missing.

[Daniel Lee]

Yes, exactly. Any data in the example file that is shown like this “” is missing.

[Nick Cox]

Perhaps this is closer to what you want.

* Example generated by -dataex-. For more info, type help dataex
clear
input int ID byte(DayofInfluenca symp1) str8 symp2 str9(symp3 symp4 symp5) str8(symp6 symp7)
1530  7 . ""         "very weak" "very weak" "weak"      ""         "moderate"
3550  6 . ""         ""          ""          "moderate"  "moderate" ""        
 400 13 . ""         ""          ""          ""          ""         ""        
 360  3 . "moderate" "moderate"  ""          "moderate"  ""         ""        
1190  5 . ""         ""          ""          ""          ""         ""        
3220  2 . ""         "moderate"  ""          ""          "moderate" ""        
 330  9 . ""         ""          ""          ""          ""         ""        
2890 10 . ""         ""          ""          "very weak" "moderate" ""        
2780  2 . ""         ""          ""          ""          ""         ""        
  60  1 . "strong"   "moderate"  ""          "moderate"  ""         "weak"    
1340  6 . ""         ""          "weak"      "weak"      ""         ""        
3660  9 . ""         "very weak" "very weak" ""          ""         ""        
3380  7 . ""         ""          ""          ""          ""         ""        
3850  9 . ""         ""          ""          ""          ""         "strong"  
4610  5 . ""         "very weak" ""          "weak"      ""         "moderate"
 590 10 . ""         ""          ""          ""          ""         ""        
4040  2 . ""         ""          ""          "strong"    ""         ""        
end

drop symp1 

reshape long symp, i(ID) j(Goodname)

bysort Day Goodname: egen Count = total(symp != "")

tabdisp Day Goodname, c(Count)