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

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

1 ACCEPTED SOLUTION

Accepted Solutions
AncaTilea
Pyrite | Level 9

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

7 REPLIES 7
ballardw
Super User

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;

AncaTilea
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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;

sbennet
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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;

sbennet
Calcite | Level 5

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

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
  • 7 replies
  • 858 views
  • 8 likes
  • 5 in conversation