percent of total

[mayasak]

Hi,

I'm working on a complex data set which includes the % susceptibility, number of isolates tested for different bug drug combinations in different hospitals. So each hospital has the following:

I ran my SAS coding and got the total numbers of isolates tested  and total number of susceptibles for each drug-bug combination in all the hospitals:

My report should have the % susceptible for all hospitals in each drug-organism (bug) combinations as below:

and:

So is there a way to aggregate the data in one % susceptible for all hospitals in each drug-bug combination instead of having them the way I did?

Thank you

[sbxkoenk]

The good news is that there are several ways to do it : data step, proc report, proc tabulate, proc iml, ...

The bad news is :

that it is not possible to program against data in a picture.
Is it possible to provide (a sample of) the input data in a data step with datalines;?
Kind regards,
Koen

[mayasak]

Thank you Koen.

Here's a data step with datalines:

data Get;

   length filename $150 hospital $150 drug_bug $500 drug $300 bug $300 OrganismName $300;

input filename $ hospital $ type $ drug_bug $ drug $ bug $ OrganismName $ isolates_hospital it ps psd isolate_hospital_n it_n ps_n ns_n psd_n nsd_n;

   datalines;

C:\Users\StMary_AB2020.xlsm StMary NM Amikacin-Ecoli Amikacin Ecoli Ecoli 54 54 96 4 54 54 0.96 52 0.04 2

C:\Users\Memorial_AB2020.xlsm Memorial NM Ampicillin_Strep Ampicillin Strep Strep 19 19 95 5 19 19 0.95 18 0.05 1

C:\Users\Medical_AB2020.xlsm Medical NM Toromycin_Enterobac Toromycin Enterobac Enterobac 195 190 88 5 195 190 0.92 175 0.05 10

;

run;

[mayasak]

Hi, I've posted the datalines and the code I used for the analysis. Just checking if you had the time to look at it!

Thank you

[sbxkoenk]

Hello,

In your code below, you do all steps "by drug_bug;".

So then it is across hospitals, no?

How comes you think your aggregates are per hospital?

Thanks,

Koen

[mayasak]

When I run the code, I get the ps_n, it_n and ns_n for each hospital in a drug_bug combination and a total for all the hospitals' ps_n, it_n and ns_n. What I need is the % of ps_n for all hospitals and not the totals.

[ballardw]

If you show the code you used for "I ran my SAS coding and got the total numbers of isolates tested and total number of susceptibles for each drug-bug combination in all the hospitals:"

I might take a stab.

Hard to suggest code without variable names, characteristics or knowledge of the data set structure.

---

@mayasak wrote:

Hi,

I'm working on a complex data set which includes the % susceptibility, number of isolates tested for different bug drug combinations in different hospitals. So each hospital has the following:

I ran my SAS coding and got the total numbers of isolates tested  and total number of susceptibles for each drug-bug combination in all the hospitals:

My report should have the % susceptible for all hospitals in each drug-organism (bug) combinations as below:

and:

So is there a way to aggregate the data in one % susceptible for all hospitals in each drug-bug combination instead of having them the way I did?

Thank you

---

[mayasak]

Thank you.

Here's the code I ran.

data final_drugbug;

  set hou.Drugbug_combined_gn_2020;

  if ns_n='.' then delete;

  if ns_n =0 then delete;

  if it_n='.' then it_n=isolates_hospital_n;

run;

And here's the data step with datalines sample:

data Get;

length filename $150 hospital $150 drug_bug $500 drug $300 bug $300 OrganismName $300;

input filename $ hospital $ type $ drug_bug $ drug $ bug $ OrganismName $ isolates_hospital it ps psd isolate_hospital_n it_n ps_n ns_n psd_n nsd_n;

   datalines;

C:\Users\StMary_AB2020.xlsm StMary NM Amikacin-Ecoli Amikacin Ecoli Ecoli 54 54 96 4 54 54 0.96 52 0.04 2

C:\Users\Memorial_AB2020.xlsm Memorial NM Ampicillin_Strep Ampicillin Strep Strep 19 19 95 5 19 19 0.95 18 0.05 1

C:\Users\Medical_AB2020.xlsm Medical NM Toromycin_Enterobac Toromycin Enterobac Enterobac 195 190 88 5 195 190 0.92 175 0.05 10

;

run;

proc sort data= final_drugbug;

  by drug_bug;

run;

proc univariate data=final_drugbug;

  by drug_bug;

  var ps_n;

  histogram;

  output out=means mean=ps_mean std=ps_std;

run;

data MDRO_2020;

  merge final_drugbug means;

  by drug_bug;

run;

proc print data=MDRO_2020;

  by drug_bug;

  sum it_n ps_n ns_n;

  var hospital drug bug isolates_hospital_n it_n ps_n ns_n;

run;

proc print data=MDRO_2020 noobs;

  where abs(ps_n-ps_mean) > 2*ps_std ;

  by drug_bug;

  var hospital drug bug isolates_hospital_n it_n ps_n ns_n;

run;

[sbxkoenk]

OK.

What is still missing in the code you have specified above?

You already work with aggregates for each drug-organism across all hospitals which is what you were looking for.

So what is it you still miss?

Thanks,

Koen

[mayasak]

The code gives me aggregates for each drug_bug combination and Ps (percent susceptible) for each hospital within the drug_bug combination. For example, for each combination I got the percent susceptibility for each hospital. What I'm looking for is an aggregate percent susceptibility for all hospitals combined, if possible.

Thank you