Transpose and concatenate

[RJY9]

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

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

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

1

1

2

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

2024-07-16,10mg

101

19-Jul-24

19-Jul-24

15-Jul-24

ICANS

1

1

2

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

[Tom]

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

[Tom]

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;

[RJY9 · Accepted solution]

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

[FreelanceReinh]

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.