Hi fellow SAS programmers, I have to fill several large table shells by rows, and I can't figure out how to somewhat automate the process. Some of them are 40-50 rows long, so typing every line by hand is possible but not practical. The underlying data isn't really relevant, but coding example for a smaller table + desired output is below. (1) The table shell (imported from an xlsx file): (2) Code for counts/numeric values: *=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
Total population for denominator in later tables
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
proc sql;
select count(distinct person_id) into :row15 from tables.study_population;
quit;
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
For patients with multiple index scripts, remove duplicated person_id to retain only 1
per patient. since all on same day, does not matter which
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
proc sort data=tables.study_population out=table1_unique nodupkey; by person_id; run;
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
Age
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
title "Age (years) summary";
proc sql;
select count(distinct person_id) into :row2 from table1_unique;
select round(mean(index_age),0.01) into :row3 from table1_unique;
select round(std(index_age),0.01) into :row4 from table1_unique;
select round(median(index_age),0.01) into :row5 from table1_unique;
quit;
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
Age Group
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
title "Age Group Summary";
proc sql;
select count(distinct person_id) into :row7 from table1_unique where age_group = 1;
select count(distinct person_id) into :row8 from table1_unique where age_group = 2;
select count(distinct person_id) into :row9 from table1_unique where age_group = 3;
select count(distinct person_id) into :row10 from table1_unique where age_group = 4;
quit;
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
Follow-up period duration
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
proc sql;
select round(mean(followup),0.01) into :row12 from table1_unique;
select round(std(followup),0.01) into :row13 from table1_unique;
select round(median(followup),0.01) into :row14 from table1_unique;
quit;
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+
Output Results
*=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+=====+;
data table1;
format count $32.;
set table1_shell;
if row=2 then count=compress(put(&row2,comma10.0));
if row=3 then count=compress(put(&row3,best.));
if row=4 then count=compress(put(&row4,best.));
if row=5 then count=compress(put(&row5,best.));
if row=7 then count=compress(put(&row7,comma10.0));
if row=8 then count=compress(put(&row8,comma10.0));
if row=9 then count=compress(put(&row9,comma10.0));
if row=10 then count=compress(put(&row10,comma10.0));
if row=12 then count=compress(put(&row12,comma10.0));
if row=13 then count=compress(put(&row13,best.));
if row=14 then count=compress(put(&row14,best.));
if row=15 then count=compress(put(&row15,comma10.0));
run; proc report data=table1; column metric count; define metric / "Metric" display style=[asis=on]; define count / "Value"; run; (3) The output table from this code: My question is: How can I convert the final data step into a macro, array, etc. to reduce the amount of code I actually have to write. For example, I have another table shell with 60 rows, but the same format, i.e. "if row = X then count = &rowX", where x = 1 to x = 60. I've tried an array + do loop, and I can't figure out what's going wrong. I'm not super adept at imagining the macro compiling, and I think that's where the issue is. I appreciate any guidance my fellow programmers are able to offer! Thanks
... View more
