PROC SQL to concatenate distinct number and associated text

[saslove]

I need to combine reasons from my dataset to look like these for the ones in the table that have a reason/cause. I have completed the programming part to create the table, but I don't know how to append the reason/cause to the code.

proc sql;
create table table as

select distinct
1 as no,
'Enrolled' as descrip,
put(count(case when enrolled=1 then 1 else . end),2.) as count
from enrolled

union
select distinct
2 as no,
'Treated' as descrip,
put(count(case when treated=1 then 1 else . end),2.) as count
from treated

union
select distinct
3 as no,
'Off-study prior to receiving therapy' as descrip,
put(count(case when offstudy_pre_trt=1 then 1 else . end),2.) as count
from offstudy_pre_trt

union
select distinct
4 as no,
'On-study, Active' as descrip,
put(count(case when off_study=0 then 1 else . end),2.) as count
from on_study

union
select distinct
5 as no,
'On-study, LTFU' as descrip,
put(count(case when ltfu=1 then 1 else . end),2.) as count
from ltfu

union
select distinct
6 as no,
'Deaths on study and cause of death' as descrip,
put(count(case when death_onstudy=1 then 1 else . end),2.) as count
from on_study

union
select distinct
7 as no,
'Off-study and reason' as descrip,
put(count(case when off_study=1 then 1 else . end),2.) as count
from on_study

order by no;
quit;

For #6 and #7, please tell me how to append the associated reason.

Thanks,

This is how the table should look:

Category	Number of Subjects
Enrolled	abc
Treated	abc with 1 retreatment
Off-study prior to receiving therapy	a
On study, active	b
On study, LTFU	aaa
Deaths while on study and cause of death	xx due to Progressive Disease cy due to Transplant Complications
Off-study and reason	ab due to death 6 due to did not receive CAR T cells

[Reeza]

catx( ' - ' ,
       put(count(case when off_study=1 then 1 else . end),2.),
       reason _text)
 as count

Change your last variable to be something like above, basically use CATX to append the data? Where does the 'reason' come from? I'm assuming it's in your data set somewhere.

---

@saslove wrote:

I need to combine reasons from my dataset to look like these for the ones in the table that have a reason/cause. I have completed the programming part to create the table, but I don't know how to append the reason/cause to the code.

proc sql;
create table table as

select distinct
1 as no,
'Enrolled' as descrip,
put(count(case when enrolled=1 then 1 else . end),2.) as count
from enrolled

union
select distinct
2 as no,
'Treated' as descrip,
put(count(case when treated=1 then 1 else . end),2.) as count
from treated

union
select distinct
3 as no,
'Off-study prior to receiving therapy' as descrip,
put(count(case when offstudy_pre_trt=1 then 1 else . end),2.) as count
from offstudy_pre_trt

union
select distinct
4 as no,
'On-study, Active' as descrip,
put(count(case when off_study=0 then 1 else . end),2.) as count
from on_study

union
select distinct
5 as no,
'On-study, LTFU' as descrip,
put(count(case when ltfu=1 then 1 else . end),2.) as count
from ltfu

union
select distinct
6 as no,
'Deaths on study and cause of death' as descrip,
put(count(case when death_onstudy=1 then 1 else . end),2.) as count
from on_study

union
select distinct
7 as no,
'Off-study and reason' as descrip,
put(count(case when off_study=1 then 1 else . end),2.) as count
from on_study

order by no;
quit;

For #6 and #7, please tell me how to append the associated reason.

Thanks,

This is how the table should look:

Category	Number of Subjects
Enrolled	abc
Treated	abc with 1 retreatment
Off-study prior to receiving therapy	a
On study, active	b
On study, LTFU	aaa
Deaths while on study and cause of death	xx due to Progressive Disease cy due to Transplant Complications
Off-study and reason	ab due to death 6 due to did not receive CAR T cells

---

[saslove]

It kinda worked but that row is repeated for every single reason. I want count due to reason , count due to reason, count due to reason. (separated by comma).

Please see attached.. 

[Reeza]

No attachment was included. CATX() is the function you need to concatenate text comments, so you'll likely want to nest it a bit. I suspect a data step would be a lot easier for this.

[saslove]

I am attaching once again.. 

In a datastep, how can I get the n and also concatenate separated by a comma on the same row.

[Reeza]

AFAIK you cannot easily do that in SQL. You’ll have to change to a data step.

[saslove]

Ok just an update on what I did.
I used proc freq and did a 2x2 to output the flags and reasons . I used data step to concatenate.