input Hospital $ Bug $ Drug $ it_n ps_n ns_n;
ABC Ecoli MC 22 0.5 11
ABC EcoliES MC 13 0.7 9.1
XYZ Kleb MN 100 0.6 60
XYZ KlebES MN 33 1 33
RTY Ecoli FL 50 0.4 20
RTY EcoliES FL 20 0.1 2
I have the above data sample. The data contains the following variables Hospital, drug, bug, it_n (number of tests, ps_n (%susceptibility) ns_n (number of susceptible tests).
Some hospitals as in the dataset have values for 2 bugs instead of one, we need to keep values for Ecoli and Kleb and remove EcoliES and KlebES based on this formula: Total number of tests susceptible to Drug/ Total number of tests, for example, we'll end up with Ecoli only and calculate the values as (11 + 9.1 / 22+ 13) = 60%, so 0.6 for ps_n 20.1 for ns_n and 35 for it_n.
Is there a way to do this in SAS?
I appreciate your help.
You may want a DOW-Loop way:
data want; do until(last.drug); set exposure; by hospital drug notsorted; sum_it_n=sum(sum_it_n,it_n); sum_ns_n=sum(sum_ns_n,ns_n); end; do until(first.drug); set exposure; by hospital drug notsorted; it_n=sum_it_n; ns_n=sum_ns_n; ps_n=ns_n/it_n; end; run;
Your sample data leaves a lot to presume. I presume that
data Exposure; input Hospital $ Bug $ Drug $ it_n ps_n ns_n; datalines; ABC Ecoli MC 22 0.5 11 ABC EcoliES MC 13 0.7 9.1 XYZ Kleb MN 100 0.6 60 XYZ KlebES MN 33 1 33 RTY Ecoli FL 50 0.4 20 RTY EcoliES FL 20 0.1 2 run; data want (drop=sum_:); set exposure; by hospital notsorted; if first.hospital then call missing(sum_itn,sum_nsn); sum_itn+it_n; sum_nsn+ns_n; if last.hospital; it_n=sum_itn; ns_n=sum_nsn; ps_n=ns_n/it_n; if find(cats(bug,'!'),'ES!') then bug=substr(bug,1,length(bug)-2); run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.