BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ketan_korde
Fluorite | Level 6

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 haveData I have

 

Following image shows the result I want:

Result I wantResult I want

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Mazi
Pyrite | Level 9
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?

View solution in original post

10 REPLIES 10
Mazi
Pyrite | Level 9
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
Fluorite | Level 6
Yes. There is Id variable and yes there is always two rows.
Mazi
Pyrite | Level 9
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
Fluorite | Level 6
This is working. Thanks
Tom
Super User Tom
Super User

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
Fluorite | Level 6
Hi Tom, Thank you for the solution its working.
Astounding
PROC Star
Will AGE always be consistent, or could the same ID have two different ages?
ketan_korde
Fluorite | Level 6
Yes, Age will be consistent.
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ketan_korde
Fluorite | Level 6
Hi mkeintz, Thank you for the solution.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1851 views
  • 3 likes
  • 5 in conversation