Creating macro/array/loop to fill large table shells by row?

irvinery — Tue, 29 Nov 2022 16:14:45 GMT

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

Re: Creating macro/array/loop to fill large table shells by row?

Tom — Tue, 29 Nov 2022 16:25:50 GMT

Seems like an extremely complex design, but I think what you are looking for is the SYMGET() function. Or perhaps even the SYMGETN() function.

count=compress(put(symgetn(cats('row',row)),comma10.0));

Re: Creating macro/array/loop to fill large table shells by row?

irvinery — Tue, 29 Nov 2022 16:45:03 GMT

I have tried something similar, but still wasn't able to figure out how to loop it to do it for all rows that needed to be filled

Re: Creating macro/array/loop to fill large table shells by row?

Tom — Tue, 29 Nov 2022 17:04:08 GMT

There is no looping involved if you have a dataset with the variable ROW and the corresponding ROWn macro variables.

data want;
  set have ;
  countn=symgetn(cats('row',row));
  countc=put(countn,comma10.);
run;

Re: Creating macro/array/loop to fill large table shells by row?

irvinery — Tue, 29 Nov 2022 17:07:15 GMT

That explains why I couldn't figure out the loop! Thanks so much

topic Re: Creating macro/array/loop to fill large table shells by row? in SAS Programming

Creating macro/array/loop to fill large table shells by row?

Re: Creating macro/array/loop to fill large table shells by row?

Re: Creating macro/array/loop to fill large table shells by row?

Re: Creating macro/array/loop to fill large table shells by row?

Re: Creating macro/array/loop to fill large table shells by row?