topic Re: PROC SQL to concatenate distinct number and associated text in SAS Programming

PROC SQL to concatenate distinct number and associated text

saslove — Fri, 18 Jan 2019 23:40:22 GMT

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

Re: PROC SQL code

Reeza — Fri, 18 Jan 2019 23:41:59 GMT

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

Re: PROC SQL to concatenate distinct number and associated text

saslove — Fri, 18 Jan 2019 23:47:20 GMT

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..

Re: PROC SQL to concatenate distinct number and associated text

Reeza — Fri, 18 Jan 2019 23:57:32 GMT

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.

Re: PROC SQL to concatenate distinct number and associated text

saslove — Sat, 19 Jan 2019 00:02:27 GMT

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.

Re: PROC SQL to concatenate distinct number and associated text

Reeza — Sat, 19 Jan 2019 05:35:35 GMT

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

Re: PROC SQL to concatenate distinct number and associated text

saslove — Tue, 22 Jan 2019 14:50:11 GMT

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.