topic Re: Row to column by ID in SAS Programming

Row to column by ID

rajd1 — Fri, 19 Jun 2020 21:43:30 GMT

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

Re: Row to column by ID

Reeza — Fri, 19 Jun 2020 21:55:12 GMT

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

Re: Row to column by ID

novinosrin — Fri, 19 Jun 2020 22:01:09 GMT

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;

Re: Row to column by ID

novinosrin — Fri, 19 Jun 2020 22:10:09 GMT

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;

Re: Row to column by ID

rajd1 — Fri, 19 Jun 2020 22:31:42 GMT

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

Re: Row to column by ID

rajd1 — Fri, 19 Jun 2020 22:32:59 GMT

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

Re: Row to column by ID

Ksharp — Sat, 20 Jun 2020 12:25:37 GMT

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;