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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.