merging records issue (HELP!!)

[gyambqt]

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                                   asdsad

B                                                             asdasd
B                                                                                                    asdsad
B

...

...

...

N

Desired Output:

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

A                                  abcdef                 sdf

B                                   asdsad              asdasd                     asdsad

[Ksharp · Accepted solution]

No, no easy way to get it.

data have;
input (Var1     Var2     Var3    ) ($);
cards;
A    abc  .  
A    def  . 
A      .        sdf      
B    asdsad     . 
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

[gyambqt]

Hello xiakeshan,

Your code worked perfectly!!!

Well done!!!

[Kurt_Bremser]

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;

[gyambqt]

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..