DATA Step, Macro, Functions and more

merging records issue (HELP!!)

Accepted Solution Solved
Reply
Regular Contributor
Posts: 152
Accepted Solution

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                                   asdsad

B                                                             asdasd
B                                                                                                    asdsad
B

...

...

...

N

Desired Output:

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

A                                  abcdef                 sdf

B                                   asdsad              asdasd                     asdsad


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

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

View solution in original post


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

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

Regular Contributor
Posts: 152

Re: merging records issue (HELP!!)

Hello xiakeshan,

Your code worked perfectly!!!

Well done!!!

Super User
Posts: 7,761

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
Regular Contributor
Posts: 152

Re: merging records issue (HELP!!)

Posted in reply to KurtBremser

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 243 views
  • 0 likes
  • 3 in conversation