topic Re: merging records issue (HELP!!) in SAS Programming

merging records issue (HELP!!)

gyambqt — Fri, 16 Jan 2015 05:09:01 GMT

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

...

Desired Output:

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

A abcdef sdf

B asdsad asdasd asdsad

Re: merging records issue (HELP!!)

Ksharp — Fri, 16 Jan 2015 07:07:18 GMT

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

Re: merging records issue (HELP!!)

Kurt_Bremser — Fri, 16 Jan 2015 07:11:32 GMT

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;

Re: merging records issue (HELP!!)

gyambqt — Fri, 16 Jan 2015 10:06:53 GMT

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

Re: merging records issue (HELP!!)

gyambqt — Fri, 16 Jan 2015 10:07:33 GMT

Hello xiakeshan,

Your code worked perfectly!!!

Well done!!!