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 |
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;
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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.