BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saslove
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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.

 

 

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
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.

 

 

--
Paige Miller
saslove
Quartz | Level 8

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1048 views
  • 0 likes
  • 2 in conversation