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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Select SAS Training centers are offering in-person courses. View upcoming courses for: