topic How to carry over data from the columns to rows in SAS Programming

How to carry over data from the columns to rows

Kbug — Tue, 29 Oct 2019 12:02:26 GMT

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

Re: How to carry over data from the columns to rows

tomrvincent — Tue, 29 Oct 2019 12:08:18 GMT

Proc Transpose is what you want to use.

Re: How to carry over data from the columns to rows

Kurt_Bremser — Tue, 29 Oct 2019 12:22:08 GMT

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

Re: How to carry over data from the columns to rows

novinosrin — Tue, 29 Oct 2019 13:15:25 GMT

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