Hello,
I am hoping to combine multiple columns into one column (by stacking them sequentially) within a dataset.
I have to do this with a substantially large number of columns.
Here is an example.
I have this:
col1 col2 col3
v1 v8 v15
v2 v9 v16
v3 v10 v17
v4 v11 v18
I want to end up with this:
col4
v1
v2
v3
v4
v8
v9
v10
v11
v15
v16
v17
v18.
Thank you for the help
Cheers,
Scott
HI.
data have;
input col1 $ col2 $ col3 $;
cards;
v1 v8 v15
v2 v9 v16
v3 v10 v17
v4 v11 v18
;
run;
data long;
set have;
%macro long();
%do i = 1 % to 3; *change this to reflect your number of columns;
col = col&i.;
output;
drop col&i.;
%end;
%mend long;
%long();
run;
Good luck;
Anca.
Not very elegant but what first comes to mind is:
Data want;
set
have (keep=col1 rename=(col1=col4))
have (keep=col2 rename=(col2=col4))
have (keep=col3 rename=(col3=col4))
;
run;
HI.
data have;
input col1 $ col2 $ col3 $;
cards;
v1 v8 v15
v2 v9 v16
v3 v10 v17
v4 v11 v18
;
run;
data long;
set have;
%macro long();
%do i = 1 % to 3; *change this to reflect your number of columns;
col = col&i.;
output;
drop col&i.;
%end;
%mend long;
%long();
run;
Good luck;
Anca.
There is no need for macro code to do this. Just use an array.
data long ;
set have;
array c col1-col3 ;
do over c ;
col=c;
output;
end;
drop col1-col3;
run;
That is a very efficient way of making this work,
Because my data is varying in the number of columns, depending on what part of the data I am working with, I need to use a macro either in the code you suggest or use as macro as suggested by Anca.
Thank you for the suggestion.
Cheers,
Scott
You could use a macro variable to store the list of variables.
%let varlist=col1-col3 var4 ;
data long ;
set have;
array c &varlist ;
do over c ;
col=c;
output;
end;
drop &varlist ;
run;
You could also do it with proc transpose. e.g.:
data have;
input (col1-col3) ($);
cards;
v1 v8 v15
v2 v9 v16
v3 v10 v17
v4 v11 v18
;
data have;
set have;
recnum=_n_;
cards;
v1 v8 v15
v2 v9 v16
v3 v10 v17
v4 v11 v18
;
proc transpose
data=have
out=want (keep=col1 rename=(col1=col4));
var col1-col3;
by recnum;
run;
Thank you everyone for all your help!
Both of the methods suggested by Arthur Tabachneck and Anca Tilea work.
Anca's works better for my application.
Thank you again for all the help!
Cheers,
Scott
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.