topic Re: Data Rollup in SAS Software for Learning Community

Data Rollup

ketan_korde — Sat, 23 Mar 2024 13:39:42 GMT

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.

Re: Data Rollup

Mazi — Sat, 23 Mar 2024 15:04:14 GMT

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?

Re: Data Rollup

ketan_korde — Sat, 23 Mar 2024 15:25:01 GMT

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

Re: Data Rollup

Mazi — Sat, 23 Mar 2024 15:43:33 GMT

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?

Re: Data Rollup

Tom — Sat, 23 Mar 2024 22:31:59 GMT

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

Re: Data Rollup

Astounding — Sat, 23 Mar 2024 23:43:34 GMT

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

Re: Data Rollup

mkeintz — Sun, 24 Mar 2024 02:10:45 GMT

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;

Re: Data Rollup

ketan_korde — Sun, 24 Mar 2024 05:24:56 GMT

Yes, Age will be consistent.

Re: Data Rollup

ketan_korde — Sun, 24 Mar 2024 05:25:55 GMT

This is working. Thanks

Re: Data Rollup

ketan_korde — Sun, 24 Mar 2024 05:26:42 GMT

Hi Tom, Thank you for the solution its working.

Re: Data Rollup

ketan_korde — Sun, 24 Mar 2024 05:28:00 GMT

Hi mkeintz, Thank you for the solution.