I have this sql code that puts out the cohorts depending on its variability per different studies. I want to use it in proc define to automatically define the columns based on the number of cohorts.
proc sql ;
select count(distinct cohort)
into :n
from adae;
select distinct cohort
into :coh1 - :coh%left(&n)
from adae;
select count (distinct subject)
into :chtsize1 - :chtsize%left(&n)
from adae
group by cohort;
select count (distinct subject)
into :COHORTCTtotal
from adae;
select distinct "'"||STRIP(cohort)||"'" into:cohortlist separated by ', '
from adae;
quit;
If you see the column statement and define statement where I have str2A, STR2B, STR1a, STR1B - these letters after strXX are the cohorts I need from coh1 - :coh%left(&n) macro in the above sql step. How do I get this in the column and define statement without having to manually type cohort names since the names and number of cohorts vary per study.
proc report data=merged split='|';
column bsoc sort term ('Dose Level (T cells/kg)' str2a str2b str1a str1b strall);
define bsoc /order noprint;
define sort / order noprint;
define term /display left style(column)={width=1in} 'System Organ Class|Preferred Term';
define str2a /display right style(column)={width=.9in} "Cohort 2A|(N=&chtsize3.)|n (%)";
define str2b /display right style(column)={width=.9in} "Cohort 2B|(N=&chtsize4.)|n (%)";
define str1a /display right style(column)={width=.9in} "Cohort 1A|(N=&chtsize1.)|n (%)";
define str1b /display right style(column)={width=.9in} "Cohort 1B|(N=&chtsize2.)|n (%)";
define strall /display right style(column)={width=.9in} "Total|(N=&COHORTCTtotal.)";
compute term;
if sort ne 1 then do;
call define(_col_,"style","style=[indent=50]");
end;
endcomp;
run;
proc report data=merged split='|';
column bsoc sort term ('Dose Level (T cells/kg)'
%do i=1 %to &n; str&&coh&i %end; strall);
define bsoc /order noprint;
define sort / order noprint;
define term /display left style(column)={width=1in} 'System Organ
Class|Preferred Term';
%do i=1 %to &n;
define str&&coh&i /display right style(column)={width=.9in}
"Cohort &&coh&i|(N=&&chtsize&i)|n (%)";
%end;
define strall /display right style(column)={width=.9in}
"Total|(N=&COHORTCTtotal.)";
compute term;
if sort ne 1 then do;
call define(_col_,"style","style=[indent=50]");
end;
endcomp;
run;
You have to call this inside a macro, as the %DO command is not valid in open code.
proc report data=merged split='|';
column bsoc sort term ('Dose Level (T cells/kg)'
%do i=1 %to &n; str&&coh&i %end; strall);
define bsoc /order noprint;
define sort / order noprint;
define term /display left style(column)={width=1in} 'System Organ
Class|Preferred Term';
%do i=1 %to &n;
define str&&coh&i /display right style(column)={width=.9in}
"Cohort &&coh&i|(N=&&chtsize&i)|n (%)";
%end;
define strall /display right style(column)={width=.9in}
"Total|(N=&COHORTCTtotal.)";
compute term;
if sort ne 1 then do;
call define(_col_,"style","style=[indent=50]");
end;
endcomp;
run;
You have to call this inside a macro, as the %DO command is not valid in open code.
Wow! This works perfectly!!! Exactly what I needed. Thanks a ton 🙂
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.