How to carry over data from the columns to rows

[Kbug]

Hello, I am using SAS 9.4 
I am currently trying to figure out how to move data from the columns of rows with the same ID and trying to move it all into the same row. There are two specific column datas that I want to move around, cname and cdate.

I have a large set of data and there are a lot of different ID's in there with different amount of rows. Here's a sample snippet

Set: Have

ID       cname         cdate   

A          org1          12/21/2018

A          org2           1/1/2018

A          org1           3/31/2018

A          org3           6/12/2018 

A          org3           11/11/2018

A          org2           12/3/2018

B         org4            1/4/2018

C         org4           4/27/2019

C         org1           1/6/2019

C         org3           2/2/2019

SET: Want 

ID      cname1, cname 2, cname 3, cname4              cdate1       cdate2   cdate3    cdate4     cdate5     cdate6    

A         org 1       org2         org3          --                    12/21/18     1/1/18    3/31/18   6/12/18   11/11/18   12/3/18

B         --              --               --          org4                  1/4/18            --          --               --           --                --

C         org1        --              org3       org4                  4/27/19      1/6/19     2/2/19        --             --              --

In the end what I want is just one row per patient with extra columns stating all the extra cnames and cdates that they have. Each ID varies and may have 1-20 cdates and 1-10 unique cnames. Is this even possible or would I just have to manually do this?

Thanks

[tomrvincent]

Proc Transpose is what you want to use.

[Kurt_Bremser · Accepted solution]

Because of the peculiar structure of your "want", you need to use two separate transpose runs:

data have;
input ID $ cname $  cdate :mmddyy10.;  
format cdate mmddyy10.;
datalines;
A          org1          12/21/2018
A          org2           1/1/2018
A          org1           3/31/2018
A          org3           6/12/2018 
A          org3           11/11/2018
A          org2           12/3/2018
B         org4            1/4/2018
C         org4           4/27/2019
C         org1           1/6/2019
C         org3           2/2/2019
;

proc sort
  data=have (keep=id cname)
  out=int1
  nodupkey
;
by id cname;
run;

data rawtrans1;
set int1;
by id;
count = substr(cname,4);
run;

proc transpose data=rawtrans1 out=trans1 (drop=_name_) prefix=cname;
by id;
var cname;
id count;
run;

data rawtrans2;
set have (keep=id cdate);
by id;
if first.id
then count = 1;
else count + 1;
run;

proc transpose data=rawtrans2 out=trans2 (drop=_name_) prefix=cdate;
by id;
var cdate;
id count;
run;

data want;
merge
  trans1
  trans2
;
by id;
run;

proc print data=want noobs;
run;

Result:

ID    cname1    cname2    cname3    cname4        cdate1        cdate2        cdate3        cdate4        cdate5        cdate6

A      org1      org2      org3               12/21/2018    01/01/2018    03/31/2018    06/12/2018    11/11/2018    12/03/2018
B                                    org4     01/04/2018             .             .             .             .             .
C      org1                org3      org4     04/27/2019    01/06/2019    02/02/2019             .             .             .

[novinosrin]

Hi @Kbug   Once you quantify the max dimensions for an array subscript, it's pretty straight forward

 
data have;
input ID $       cname $         cdate :mmddyy10.;
format cdate mmddyy10.;
cards;
A          org1          12/21/2018
A          org2           1/1/2018
A          org1           3/31/2018
A          org3           6/12/2018 
A          org3           11/11/2018
A          org2           12/3/2018
B         org4            1/4/2018
C         org4           4/27/2019
C         org1           1/6/2019
C         org3           2/2/2019
;
/*Get the max dimensions for the array subscript*/
proc sql noprint; 
 select count(distinct cname) into :c_n trimmed from have;
 select max(c) into :c_d trimmed
 from (select  count(distinct cdate) as c from have group by id);
 quit;

%put &=c_n &=c_d;

data want;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  array cname_(&c_n) $;
  array cdate_(&c_d);
  k=input(char(cname,length(cname)),best.);
  cname_(k)=cname;
  cdate_(_n_)=cdate;
 end;
 format cdate_: mmddyy10.;
 drop k;
run;

EDITED: A typo