Data Rollup

[ketan_korde]

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.

[Mazi]

Does your data not have any other columns such as an ID ?

With only two records it’s difficult to say which approach would work best for your data.

For instance, are there always two rows?
1 for the name and another for last name?

[ketan_korde]

Yes. There is Id variable and yes there is always two rows.

[Mazi · Accepted solution]

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?

[ketan_korde]

This is working. Thanks

[Tom]

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

[ketan_korde]

Hi Tom, Thank you for the solution its working.

[Astounding]

Will AGE always be consistent, or could the same ID have two different ages?

[ketan_korde]

Yes, Age will be consistent.

[mkeintz]

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;

[ketan_korde]

Hi mkeintz, Thank you for the solution.