Code for data aggregation and sum

[mayasak]

I have the following conditions and the code I created. for a data set with hospital, drug, bug, it_n, ns_n, and ps_n variables. But for #2 it's not populating the correct calculation required for ps_n value for Escherichia coli. Here's the requirements:

For only 3 hospitals Hospital1, Hospital2, and Hospital3

If "bug" = Escherichia coli and "bug" = Escherichia coli ESBL

1. Aggregate ns_n for Escherichia coli + ns_n for Escherichia coli ESBL, same thing for it_n

2. Calculate the new ps_n for "Escherichia coli" as the sum of ns_n divided by the sum of it_n.

3. Delete the row with Escherichia coli ESBL.

4. Recalculate ns_n for the "Escherichia coli" row based on the new ps_n and the sum of it_n.

Is there anything to change the code to get the correct values

data GN_Drugbug_Modified;
    set GN_Drugbug_Filtered;
    by hospital drug bug;

    retain temp_ns_n 0 temp_it_n 0 new_ps_n;

    /* Initialize retained variables for each hospital-drug group */
    if first.drug then do;
        temp_ns_n = 0;
        temp_it_n = 0;
        new_ps_n = .;
    end;

    /* Summing for Escherichia coli and Escherichia coli ESBL for specific hospitals */
    if hospital in ("Hospital1", "Hospital2", "Hospital3") and
       bug in ("Escherichia coli", "Escherichia coli ESBL") then do;
        temp_ns_n + ns_n;
        temp_it_n + it_n;
    end;

    /* Calculate new ps_n for Escherichia coli */
    if last.drug and bug = "Escherichia coli" then new_ps_n = temp_ns_n / temp_it_n;

    /* Apply new ps_n and recalculate ns_n for Escherichia coli */
    if bug = "Escherichia coli" then do;
        ps_n = new_ps_n;
        ns_n = ps_n * temp_it_n;
    end;

    /* Only keep rows that are not Escherichia coli ESBL */
    if bug ne "Escherichia coli ESBL" then output;
run;

[PaigeMiller · Accepted solution]

SAS is an extremely powerful software for doing statistical analysis. You do not have to (and should not) try to program sums by group yourself because SAS has already programmed this for you. This is done in PROC MEANS and PROC SUMMARY.

Please state clearly the statistics and other information you would like from this data.

[mayasak]

Thank you Paige for your hint. I solved the issue using proc summary.