How can I develop MACROS that helps me make a Demographic table that looks like this ?
ESRD Group | ||
Characteristic | No. of patients | No. of patients |
Total cohort | ||
Gender | ||
Female | ||
Male |
Peritoneal | ||
Hemodialysis | ||
Mean | Median (IQR) | |
Age | ||
Enrollment Duration |
The code that i wrote is this :-
*CREATING A MACRO TO STORE THE TOTAL NUMBER OF PATEINTS AS A DYNAMIC DENOMINATOR */
%macro denom (whrcls=, label=, type= ) ;
proc sql ;
select N(enrolid) into: denom
from derived._01_esrd;
quit ;
data _02_demographics ;
set derived._01_esrd ;
where &whrcls. ;
run ;
%put the denominator is &denom. yay this macro worked;
proc means data=_02_stat mean median Q1 Q3;
var age enrol_dur;
output out=stat_age mean()= median()= STD()= Q1()= Q3()= Min()= Max()=/autoname;
run;
/*CREATING THE DEMOGRAPHICS TABLE */
proc sql ;
create table _02_demo_&type.
select 1 as col, &label. as cat, N(enrolid) as N, N(enrolid)/&denom. as Percent from _02_demographics union ;
quit ;
%mend denom(whrcls=,label=) ;
%denom(PatiendID, "Total Cohort" ) ;
%denom(not PatientID, "Gender") ;
%denom(sex = 2, "Female") ;
%denom(sex =1, "Male") ;
%denom(not PatientID, "Type of Dialysis");
%denom(code in ('90935','90937','90999','99512','90945','90947'), "Dialysis") ;
%denom(PatientID,"Age") ;
%denom(PatientID,"Enrollment Duration");
I want to create a MACROS for the serial number of rows : - eg where i have written select 1 as col, I want a MACROS which can go to the next number , 2 as col etc.
The usual advice, which I think applies here, is for you to get the code running without macros, for just one condition. Once you do that, turning it into a macro should be relatively easy. Can you show us such code, without macros, that works for just one condition?
/*CREATING A MACRO TO STORE THE TOTAL NUMBER OF PATEINTS AS A DYNAMIC DENOMINATOR */
%macro denom (whrcls=, label=, type= ) ;
proc sql ;
create table _02_dt
select N(enrolid) into: denom
from derived._01_esrd;
quit ;
%put the denominator is &denom. yay this macro worked;
proc means data=_02_stat mean median Q1 Q3;
var age enrol_dur;
output out=stat_age mean()= median()= STD()= Q1()= Q3()= Min()= Max()=/autoname;
run;
/*DEMO TABLE WITHOUT MACROS*/
proc sql ;
create table _02_demo
select 1 as seq, "Total Cohort" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where enrolid union
select 2 as seq, "Gender" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where not enrolid union
select 3 as seq, "Female" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where sex=2 union
select 4 as seq, "Male" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where sex=1 union
select 5 as seq, "Type of Dialysis" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where not enrolid union
select 6 as seq, "Dialysis" as col, N(enrolid) as N, N(enrolid)/&denom. as percent, from _02_dt where code in ('90935','90937','90999','99512','90945','90947') union
select 7 as seq, "Age" as col, mean_age as N, median_age as percent from _02_stat where PatientID union
select 8 as seq, "Enrollment Duration" as col, mean_age as N, median_age as percent from _02_stat where PatientID ;
quit ;
This is my code
Does this code, without macros actually work, or does it have errors?
Is the data set created by PROC SQL, named _02_dt, related to the data set used in PROC MEANS, which is named _02_stat?
Your PROC SQL still uses the macro variable &denom, I think it would be helpful to have working code without macros and without macro variables.
*CREATING A MACRO TO STORE THE TOTAL NUMBER OF PATEINTS AS A DYNAMIC DENOMINATOR */
%macro denom ;
proc sql ;
select N(enrolid) into:denom
from derived._01_esrd;
quit ;
%put the denominator is &denom. yay this macro worked;
proc means data=derived._01_esrd mean median Q1 Q3;
var age enrol_dur;
output out=stat_age mean()= median()= STD()= Q1()= Q3()= Min()= Max()=/autoname;
run;
/*DEMO TABLE WITHOUT MACROS*/
proc sql ;
create table _02_demo as
select 1 as seq, "Total Cohort" as col, N(enrolid) as N, N(enrolid)/&denom. as percent from derived._01_esrd where enrolid union
select 2 as seq, "Gender" as col, N(enrolid) as N, N(enrolid)/&denom. as percent from derived._01_esrd where not enrolid union
select 3 as seq, "Female" as col, N(enrolid) as N, N(enrolid)/&denom. as percent from derived._01_esrd where sex='2' union
select 4 as seq, "Male" as col, N(enrolid) as N, N(enrolid)/&denom. as percent from derived._01_esrd where sex='1' union
select 5 as seq, "Type of Dialysis" as col, N(enrolid) as N, N(enrolid)/&denom. as percent from derived._01_esrd where not enrolid union
select 6 as seq, "age" as col, age_mean as N, age_median as percent from stat_age union
select 7 as seq, "Enrollment Duration" as col, enrol_dur_mean as N, enrol_dur_median as percent from stat_age
;
quit ;
%mend denom;
%denom ;
ods csv file = "/data1/projects/yellow/YVM0002aq/output/demo.csv" ;
proc print data =_02_demo ;
run ;
ods csv close ;
This is what my actual code looks like after alterations. I want a MACROS for the numbers as seq and for the where condition as parameters
But does this code actually run and produce usable results?
Did you read my previous message ?
You haven't really shared much about your data.
Here is a similar set of tables using one of the SAS supplied data sets.
proc tabulate data=sashelp.class; class sex age; var height weight; table all='Total' sex , age * n=' ' ; table height weight, mean median qrange='IQR' q1 q3 ; run;
There are lots of appearance options.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.