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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1062 views
  • 0 likes
  • 2 in conversation