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
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
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
Hello xiakeshan,
Your code worked perfectly!!!
Well done!!!
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;
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..
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.