Combine columns within a dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Combine columns within a dataset

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


Accepted Solutions
Solution
‎01-22-2013 10:39 AM
Super Contributor
Posts: 543

Re: Combine columns within a dataset

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.

View solution in original post


All Replies
Super User
Posts: 11,336

Re: Combine columns within a dataset

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;

Solution
‎01-22-2013 10:39 AM
Super Contributor
Posts: 543

Re: Combine columns within a dataset

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.

Super User
Super User
Posts: 7,035

Re: Combine columns within a dataset

Posted in reply to AncaTilea

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;

Occasional Contributor
Posts: 15

Re: Combine columns within a dataset

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

Super User
Super User
Posts: 7,035

Re: Combine columns within a dataset

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;

PROC Star
Posts: 7,467

Re: Combine columns within a dataset

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;

Occasional Contributor
Posts: 15

Re: Combine columns within a dataset

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 291 views
  • 8 likes
  • 5 in conversation