I want to roll-up data where two entries should be converted into one. So can anyone please suggest a code for the same.
Following image shows the example of data I have:
Data I have
Following image shows the result I want:
Result I want
Thanks in advance.
data have;
infile datalines dlm=',';
input id name $ age;
datalines;
0,Eric,30
0,Bishoff,30
1,Dave,60
1,Batista,60
;
run;
data want;
length name firstname $200;
set have;
by id;
retain firstname '';
if first.id then firstname=name;
else if last.id then do;
name = catx(' ', firstname, name);
output;
end;
drop firstname;
run;
can you try this?
data have;
infile datalines dlm=',';
input id name $ age;
datalines;
0,Eric,30
0,Bishoff,30
1,Dave,60
1,Batista,60
;
run;
data want;
length name firstname $200;
set have;
by id;
retain firstname '';
if first.id then firstname=name;
else if last.id then do;
name = catx(' ', firstname, name);
output;
end;
drop firstname;
run;
can you try this?
If you are positive you always have 2 and only 2 observations then something like this should work:
data have ;
input id name :$20. age ;
cards;
1 Bruce 30
1 Wayne 30
2 Dick 18
2 Grayson 18
3 Alfred 50
3 Pennyworth 50
;
data want;
set have(in=in1 rename=(name=firstname));
by id;
set have(in=in2 rename=(name=lastname) firstobs=2);
if first.id;
run;
Result:
Obs id firstname age lastname 1 1 Bruce 30 Wayne 2 2 Dick 18 Grayson 3 3 Alfred 50 Pennyworth
If you might have 1,2 or more then perhaps something like this instead:
data want;
do until(last.id);
set have;
by id;
length fullname $50;
fullname=catx(' ',fullname,name);
end;
drop name;
run;
Result
Obs id age fullname 1 1 30 Bruce Wayne 2 2 18 Dick Grayson 3 3 50 Alfred Pennyworth
If you always have exactly 2, with first name always preceding last name:
data want;
merge have (keep=id name rename=(name=firstname))
have (firstobs=2 rename=(name=lastname));
if mod(_n_,2)=1;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Ready to level-up your skills? Choose your own adventure.