topic percent of total in SAS Programming

percent of total

mayasak — Sat, 04 Dec 2021 08:35:29 GMT

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

Re: percent of total

sbxkoenk — Sat, 04 Dec 2021 13:03:52 GMT

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

Re: percent of total

ballardw — Sat, 04 Dec 2021 18:56:45 GMT

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

Re: percent of total

mayasak — Sun, 05 Dec 2021 01:39:51 GMT

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;

Re: percent of total

mayasak — Sun, 05 Dec 2021 01:53:18 GMT

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;

Re: percent of total

sbxkoenk — Sun, 05 Dec 2021 13:29:10 GMT

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

Re: percent of total

mayasak — Sun, 05 Dec 2021 17:55:32 GMT

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

Re: percent of total

mayasak — Wed, 08 Dec 2021 21:39:20 GMT

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

Re: percent of total

sbxkoenk — Thu, 09 Dec 2021 12:36:34 GMT

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

Re: percent of total

mayasak — Thu, 09 Dec 2021 15:32:46 GMT

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.