sas macros

[manya92]

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. 

[PaigeMiller]

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?

[manya92]

/*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

[PaigeMiller]

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.

[manya92]

*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

[PaigeMiller]

But does this code actually run and produce usable results?

[manya92]

Did you read my previous message ?

[ballardw]

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.