## merging records issue (HELP!!)

Solved
Regular Contributor
Posts: 155

# merging records issue (HELP!!)

Hello expert,

I want to know if there is an easy way to produce the desired output using original dataset as following:

( there is only one variable that has value for each record)

Original Dataset:

Variable 1                Variable 2            Variable 3               ........................         Variable10000000

A                                  abc

A                                  def

A                                                              sdf

B                                                             asdasd
B

...

...

...

N

Desired Output:

Variable 1                Variable 2            Variable 3               ........................         Variable10000000

A                                  abcdef                 sdf

Accepted Solutions
Solution
‎01-16-2015 02:07 AM
Super User
Posts: 10,778

## Re: merging records issue (HELP!!)

No, no easy way to get it.

```
data have;
input (Var1     Var2     Var3    ) (\$);
cards;
A    abc  .
A    def  .
A      .        sdf
B      .     asdasd
B      . . .
;
run;
data want;
set have;
by var1;
array x{*} \$ var2-var3;
array y{*} \$ 100 _var2-_var3;
retain _:;
if first.var1 then call missing(of y{*});
do i=1 to dim(x);
y{i}=cats(y{i},x{i});
end;
if last.var1;
drop i var2-var3;
run;
```

Xia Keshan

All Replies
Solution
‎01-16-2015 02:07 AM
Super User
Posts: 10,778

## Re: merging records issue (HELP!!)

No, no easy way to get it.

```
data have;
input (Var1     Var2     Var3    ) (\$);
cards;
A    abc  .
A    def  .
A      .        sdf
B      .     asdasd
B      . . .
;
run;
data want;
set have;
by var1;
array x{*} \$ var2-var3;
array y{*} \$ 100 _var2-_var3;
retain _:;
if first.var1 then call missing(of y{*});
do i=1 to dim(x);
y{i}=cats(y{i},x{i});
end;
if last.var1;
drop i var2-var3;
run;
```

Xia Keshan

Regular Contributor
Posts: 155

Hello xiakeshan,

Well done!!!

Super User
Posts: 10,237

## Re: merging records issue (HELP!!)

I pack everything in a macro so I can iteratively create statements. I rename the original Variables, create new Variables with the same name, retain them (so that their values are kept over datastep iterations) and concatenate the original Values in the new Variables, using by group processing (it is assumed that dataset have is sorted by variable1). At the end, one record is output for each distinct value of Variable1.

%macro do_it;

data want (drop=

%do i = 2 %to 10000000;

oldVariable&i

%end;

);

set have (rename=(

%do i = 2 %to 10000000;

Variable&i=oldVariable&i

%end;

));

by Variable1;

%do i = 2 %to 10000000;

length Variable&i \$100;

retain Variable&i;

%end;

if first Variable1 then do;

%do i = 2 %to 10000000;

Variable&i = '';

%end;

end;

%do i = 2 %to 10000000;

Variable&i = compress(Variable&i) !! compress(oldVariable&i);

%end;

if last.Variable1 then output;

run;

%mend;

%do_it;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 155

## Re: merging records issue (HELP!!)

Hi KurtBremser

Thanks for your response. But the variable1 .... variable 10000 are just examples to show there are many variables in the dataset. The actual variable name can be quite different..

🔒 This topic is solved and locked.