topic Re: Combine columns within a dataset in SAS Programming

Combine columns within a dataset

sbennet — Tue, 22 Jan 2013 15:31:16 GMT

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

v10

v11

v15

v16

v17

v18.

Thank you for the help

Cheers,

Scott

Re: Combine columns within a dataset

ballardw — Tue, 22 Jan 2013 15:38:01 GMT

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;

Re: Combine columns within a dataset

AncaTilea — Tue, 22 Jan 2013 15:39:24 GMT

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.

Re: Combine columns within a dataset

art297 — Tue, 22 Jan 2013 15:51:40 GMT

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;

Re: Combine columns within a dataset

sbennet — Tue, 22 Jan 2013 16:25:08 GMT

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

Re: Combine columns within a dataset

Tom — Tue, 22 Jan 2013 16:41:45 GMT

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;

Re: Combine columns within a dataset

sbennet — Tue, 22 Jan 2013 16:53:24 GMT

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

Re: Combine columns within a dataset

Tom — Tue, 22 Jan 2013 18:10:19 GMT

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;