Row to column by ID

[rajd1]

Hi,

Here is my dataset. I would like to have one row for each ID. 

Data: Have

ID	D_DATE	AA	BB	CC	DD
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3
2	10/30/2015	0	0	0	0
2	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3
3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0
4	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3
5	6/16/2016	0	0	0	0

Data: Want: Notice that not all IDs have the same number of repeated values. IDs 1 and 2 have 3 repeated measurements and IDs 3,4,5 have only 2.

ID	D_Date1	AA1	BB1	CC1	DD1	D_Date2	AA2	BB2	CC2	DD2	D_Date3	AA3	BB3	CC3	DD3
1	6/10/2015	5	4	0	0	7/10/2015	7	0	0	3	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3	10/30/2015	0	0	0	0	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3	6/16/2016	0	0	0	0

[Reeza]

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

You need to transpose your data. The second example in the first link is what you want.

---

@rajd1 wrote:

Hi,

Here is my dataset. I would like to have one row for each ID. 

Data: Have

ID	D_DATE	AA	BB	CC	DD
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3
2	10/30/2015	0	0	0	0
2	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3
3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0
4	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3
5	6/16/2016	0	0	0	0

Data: Want: Notice that not all IDs have the same number of repeated values. IDs 1 and 2 have 3 repeated measurements and IDs 3,4,5 have only 2.

ID	D_Date1	AA1	BB1	CC1	DD1	D_Date2	AA2	BB2	CC2	DD2	D_Date3	AA3	BB3	CC3	DD3
1	6/10/2015	5	4	0	0	7/10/2015	7	0	0	3	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3	10/30/2015	0	0	0	0	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3	6/16/2016	0	0	0	0

---

[rajd1]

Thanks a lot! this works great when there is a limited number of variables.

[novinosrin]

Hi @rajd1  Please see if this helps-

data have;
input ID	D_DATE :mmddyy10.	AA	BB	CC	DD;
format D_DATE mmddyy10.;	
cards;
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3
2	10/30/2015	0	0	0	0
2	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3
3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0
4	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3
5	6/16/2016	0	0	0	0
;

data temp/view=temp;
 do n=1 by 1 until(last.id);
  set have;
  by id;
  array t 	AA--DD;
  do over t;
   vn=vname(t);
   temp=t;
   output;
  end;
 end;
 drop aa--dd;
run;

proc transpose data=temp out=want(drop=_:);
 by id ;
 var temp;
 id vn n;
run;

[novinosrin · Accepted solution]

Hi again @rajd1  My apologies as I missed to include the D_date variable series in the output in my previous post. Here below, I have modified that piece too to meet your requirement.

data have;
input ID	D_DATE :mmddyy10.	AA	BB	CC	DD;
format D_DATE mmddyy10.;	
cards;
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3
2	10/30/2015	0	0	0	0
2	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3
3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0
4	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3
5	6/16/2016	0	0	0	0
;

data temp/view=temp;
 do n=1 by 1 until(last.id);
  set have;
  by id;
  array t D_DATE	AA--DD;
  do over t;
   vn=vname(t);
   temp=t;
   output;
  end;
 end;
 drop D_DATE aa--dd;
run;

proc transpose data=temp out=want(drop=_:);
 by id ;
 var temp ;
 id vn n;
 format d_date: mmddyy10.;
run;

[rajd1]

Thanks a lot! this works great with number of variables. I am dealing with 100's of them :). Thanks a lot again Sir

[Ksharp]

Merge Skill:

data have;
input ID	D_DATE :mmddyy10.	AA	BB	CC	DD;
format D_DATE mmddyy10.;	
cards;
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
1	6/10/2015	5	4	0	0
1	7/10/2015	7	0	0	3
1	9/10/2015	0	0	3	0
2	10/2/2015	0	1	0	3
2	10/30/2015	0	0	0	0
2	12/10/2015	0	3	0	0
3	1/8/2016	0	0	5	3
3	2/19/2016	0	2	0	0
4	3/11/2016	0	0	6	0
4	4/7/2016	0	2	0	0
5	5/11/2016	0	0	0	3
5	6/16/2016	0	0	0	0
;
data temp;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql noprint nowarn;
select distinct catt('temp(where=(n=',n,') rename=(
d_date=d_date',n,' aa=aa',n,' bb=bb',n,' cc=cc',n,' dd=dd',n,'))')
into : merge separated by ' '
from temp
order by n;
quit;
data want;
 merge &merge;
 by id;
drop n;
run;