PROC REPORT macro for define and column statement

[saslove]

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;

[PaigeMiller · Accepted solution]

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.

[saslove]

Wow! This works perfectly!!! Exactly what I needed. Thanks a ton 🙂