BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
irvinery
Fluorite | Level 6

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):

irvinery_0-1669737936381.png

(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:

irvinery_1-1669738152703.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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));
irvinery
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;

 

irvinery
Fluorite | Level 6
That explains why I couldn't figure out the loop! Thanks so much

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 826 views
  • 0 likes
  • 2 in conversation