turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- sas macros

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to manya92

4 weeks ago

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?

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PaigeMiller

4 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to manya92

4 weeks ago

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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PaigeMiller

4 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to manya92

4 weeks ago - last edited 4 weeks ago

But does this code actually run and produce usable results?

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PaigeMiller

4 weeks ago

Did you read my previous message ?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to manya92

4 weeks ago

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.