Sum mulptiple rows in Stata

[Maria Xenou]

Dear stata experts,
I would appreciate your advice, please. I have 93 IDs with multiple rows each and multiple visits & cost per hospital per ID. How can I create a new variable adding/summing all the visits per ID? For example, my data looks like below and I would like to have one row per ID adding the TOTAL_visits_per_hospital= 35 & Total_cost_per_ Hospital = £3500. How can I do this, please? many thanks, Maria

ID TOTAL_visits_per_hospital Total_cost_per_ Hospital

1 10 £1000
1 20 £2000
1 5 £500

[Salvatore Viola]

Dear Maria,

Thank you for your question. I wrote some code that should do what you want according to the tests that I have completed. The code itself is quite unattractive. I am sure that there is another person on this forum who could have written a cleaner code. However, getting the attention of such a person can often be difficult.

global varlist TOTAL_visits_per_hospital Total_cost_per_Hospital

local N = _N

foreach a of var $varlist{
    forvalue x = 1/`N'{    
        
        local idc = ID[`x']
        
        if `idc' == ID[`x']{
            local example`a' = `a'[`x'] 
            local examples`a'`idc'  = `examples`a'`idc'' + `example`a''
        }
        
    }
    gen Total_`a' =.
    forvalue x = 1/`N'{
        local ids = ID[`x']
        replace Total_`a' = `examples`a'`ids'' if `ids' == ID
        
    }
    drop `a'
}

quietly bysort ID:  gen dup = cond(_N==1,0,_n)
drop if dup >1
drop dup

The first line of the code is adaptable to your data. The variables following "global varlist" can be any variable in your data set that you'd like. I have already input the two that you gave in the example above.

I hope I was of some help and please let me know if the code works for you.

Best,
Salvatore

[Rich Goldstein]

there is little information to go on but the question appears much simpler to me - use -collapse-; see

h collapse

[Maria Xenou]

thank you both for your time - I will have a look now
Maria