Using macros in reports

[mayasak]

I'm preparing a report that shows % susceptibilities for different drug-bugs combinations, histogram, IQR for % susceptibles, number of hospitals, number of isolates (ns_n) and number of susceptibles (ps_n)  and I'm using the following: 

proc sort data= drugbug_combined out=drugbug;
by drug_bug;
run;

data 2018_drugbug;
set drugbug;
if ns_n='.' then delete;
if ns_n =0 then delete;
run;

data final_drugbug;
set 2018_drugbug;
if it_n='.' then do;
it_n=isolates_hospital_n;
end;
run;
proc univariate data=final_drugbug;
by drug_bug;
var ps_n;
where drug_bug='Drug1 bug 1';
histogram;
run;
data Ami_ab;
set final_drugbug;
where drug_bug='Drug1 bug 1';
run;

proc sql noprint;;
select mean (ps_n) into: mean from Ami_ab;
select std (ps_n) into : std from Ami_ab;
quit;
data Ami_abQC;
set Ami_ab;
where drug_bug='Drug 1 bug 1' AND
ps_n< &mean-2*&std OR ps_n > &mean+2*&std;
proc print noobs;
var hospital drug_bug drug bug isolates_hospital_n it_n ps_n ns_n;
run;

proc print data=Ami_ab;
sum it_n ps_n ns_n;
var hospital drug_bug drug bug isolates_hospital_n it_n ps_n ns_n;
run;

I would like to use a macro since I have more than 25 different drug bug combinations.

Thank you

[Reeza]

What specifically do you need help with?

What parts are changing from each iteration? The first link below is a tutorial that walks you through converting a working program to a macro program with an automatic call for all possible values in one variable. 

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

Spoiler

---

@mayasak wrote:

I'm preparing a report that shows % susceptibilities for different drug-bugs combinations, histogram, IQR for % susceptibles, number of hospitals, number of isolates (ns_n) and number of susceptibles (ps_n)  and I'm using the following: 

proc sort data= drugbug_combined out=drugbug;
by drug_bug;
run;

data 2018_drugbug;
set drugbug;
if ns_n='.' then delete;
if ns_n =0 then delete;
run;

data final_drugbug;
set 2018_drugbug;
if it_n='.' then do;
it_n=isolates_hospital_n;
end;
run;
proc univariate data=final_drugbug;
by drug_bug;
var ps_n;
where drug_bug='Drug1 bug 1';
histogram;
run;
data Ami_ab;
set final_drugbug;
where drug_bug='Drug1 bug 1';
run;

proc sql noprint;;
select mean (ps_n) into: mean from Ami_ab;
select std (ps_n) into : std from Ami_ab;
quit;
data Ami_abQC;
set Ami_ab;
where drug_bug='Drug 1 bug 1' AND
ps_n< &mean-2*&std OR ps_n > &mean+2*&std;
proc print noobs;
var hospital drug_bug drug bug isolates_hospital_n it_n ps_n ns_n;
run;

proc print data=Ami_ab;
sum it_n ps_n ns_n;
var hospital drug_bug drug bug isolates_hospital_n it_n ps_n ns_n;
run;

I would like to use a macro since I have more than 25 different drug bug combinations.

Thank you

---

[mayasak]

Thank you, the drug_bug variable is the one changing from each iteration.

[Tom · Accepted solution]

Why not just do them all at once?

The beginning of your code is doing that.

data final_drugbug;
  set drugbug_combined;
  if ns_n='.' then delete;
  if ns_n =0 then delete;
  if it_n='.' then it_n=isolates_hospital_n;
run;

proc sort data= final_drugbug;
  by drug_bug;
run;

So just have the rest of the code also use BY grouping.

proc univariate data=final_drugbug;
  by drug_bug;
  var ps_n;
  histogram;
  output out=means mean=ps_mean std=ps_std; 
run;

data Ami_abQC;
  merge final_drugbug means;
  by drug_bug;
run;

proc print data=Ami_abQC;
  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=Ami_abQC 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;

[mayasak]

Thank you Tom. It worked but I have a question. Is there a way that we get aggregated, hospital numbers, ps_n (percent susceptibility) and # of isolates (ns_n)  data for each drug-bug combinations as in the table below?. 

[PaigeMiller]

I am very skeptical that you need macros here.

This seems to be the key piece of code here:

where drug_bug='Drug 1 bug 1' AND
ps_n< &mean-2*&std OR ps_n > &mean+2*&std;

you are searching for records where the value of ps_n is ±2 standard deviations or more from the mean.

What you can do is use PROC STDIZE to get this information. Using a BY statement in PROC STDIZE, you can have all 25 possible drug bug combinations calculated at once.

Example on something that looks like your data, but probably won't actually work on your data without modification.

proc stdize data=whatever oprefix=o_ sprefix=s_ out=want;
by drug_bug;
var ps_n;
run;