BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gyambqt
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

4 REPLIES 4
Ksharp
Super User

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

Hello xiakeshan,

Your code worked perfectly!!!

Well done!!!

Kurt_Bremser
Super User

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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