topic Re: Transpose and concatenate in SAS Programming

Transpose and concatenate

RJY9 — Sun, 21 Sep 2025 19:21:16 GMT

Hi,

This is the input data.

USUBJID

ASTDT

AENDT

CTRTSDT

AESCAT

AETOXGR

AETOXGRN

COHORT

durg1

durg2

durg3

durg4

durg5

101

15-Jul-24

19-Jul-24

15-Jul-24

CRS

2024-07-16,10mg

101

15-Jul-24

19-Jul-24

15-Jul-24

CRS

2024-07-15,700mg

101

15-Jul-24

19-Jul-24

15-Jul-24

CRS

2024-07-16,640mg

101

19-Jul-24

15-Jul-24

ICANS

2024-07-19,100mg

101

19-Jul-24

15-Jul-24

ICANS

2024-07-16,10mg

101

19-Dec-24

20-Dec-24

16-Dec-24

CRS

2024-12-20,10mg

I need output dataset.

USUBJID

ASTDT

AENDT

CTRTSDT

AESCAT

AETOXGR

AETOXGRN

COHORT

durg1

durg2

durg3

durg4

durg5

101

15-Jul-24

19-Jul-24

15-Jul-24

CRS

2024-07-15,700mg; 2024-07-16,640mg

2024-07-16,10mg

101

19-Jul-24

15-Jul-24

ICANS

2024-07-19,100mg

2024-07-16,10mg

I tried different methods but could not able to get the right output. could you kindly help. Thanks, Raj

Re: Transpose and concatenate

Tom — Sun, 21 Sep 2025 21:25:49 GMT

What did you do manually to convert from one to the other? It does not look like either a TRANSPOSITION nor a CONCATTENATION to me.

Also how did you get the data in that form to begin with? it might be easier to start from an earlier version of the data that does not have same dates repeated multiple times.

PS Better to share the data in a re-usable form than posting copies of listings.

data have;
  infile cards dsd dlm='|' truncover;
  input USUBJID $ ASTDT :date. AENDT :date. CTRTSDT :date. 
        AESCAT $ AETOXGR $ AETOXGRN COHORT $ (drug1-drug5) (:$20.)
  ;
  format ASTDT AENDT CTRTSDT date9.;
cards;
101|15-Jul-24|19-Jul-24|15-Jul-24|CRS|1|1|2| | |2024-07-16,10mg| | 
101|15-Jul-24|19-Jul-24|15-Jul-24|CRS|1|1|2|2024-07-15,700mg| | | | 
101|15-Jul-24|19-Jul-24|15-Jul-24|CRS|1|1|2|2024-07-16,640mg| | | | 
101|19-Jul-24|19-Jul-24|15-Jul-24|ICANS|1|1|2| |2024-07-19,100mg| | | 
101|19-Jul-24|19-Jul-24|15-Jul-24|ICANS|1|1|2| | |2024-07-16,10mg| | 
101|19-Dec-24|20-Dec-24|16-Dec-24|CRS|1|1|8| | |2024-12-20,10mg| | 
;

Re: Transpose and concatenate

Tom — Sun, 21 Sep 2025 21:54:23 GMT

Is this what you are trying to do?

Collapse to one record per group as defined by those first N variables?

data want;
  do until(last.cohort);
    set have;
    by usubjid -- cohort ;
    array old drug1-drug5 ;
    array new $50 _drug1-_drug5 ;
    do over new;
      new = catx(';',new,old);
    end;
  end;
  drop drug1-drug5;
  rename _drug1-_drug5 = drug1-drug5 ;
run;

Re: Transpose and concatenate

RJY9 — Mon, 22 Sep 2025 20:53:03 GMT

Thank you so much for your swift response. yes, it is what I expected.

Re: Transpose and concatenate

FreelanceReinh — Mon, 22 Sep 2025 21:55:16 GMT

Hello @RJY9,

Glad to see that Tom's solution worked for you. Then it would be fair and help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select his post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.