Help using Base SAS procedures

all column stacked in one

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

all column stacked in one

Hello

I have a dataset as followings:

col1  col2 col3 .....col21

1          2         6      3

77          55     76     99

......          ..     ..     ..

1              7       6     7

I need to stack all on these columns on top of each other, any suggestions?

thanks


Accepted Solutions
Solution
‎01-29-2014 11:17 AM
Regular Contributor
Posts: 220

Re: all column stacked in one

"data" inside the macro %do loop is the name of your dataset as you described above.  "stacked" is the name of the dataset that will be produced after you run the macro.

View solution in original post


All Replies
Regular Contributor
Posts: 220

Re: all column stacked in one

If you know the number of columns, you could try:

data stacked;

     set

          %do i=1 %to 21;

               data (keep=col&i rename=(col&i=col))

          %end;

     ;

run;

Regular Contributor
Posts: 220

Re: all column stacked in one

Of course, you can't use the macro DO loop in open code, so you would have to enclose it within a macro:

%macro stack();

     data stacked;

          set

               %do i=1 %to 21;

                    data (keep=col&i rename=(col&i=col))

               %end;

        ;

     run;

%mend stack;

%stack();

Regular Contributor
Posts: 161

Re: all column stacked in one

I am pretty novice, please let me know where to write the name of my data file and the out datafile

thanks

Solution
‎01-29-2014 11:17 AM
Regular Contributor
Posts: 220

Re: all column stacked in one

"data" inside the macro %do loop is the name of your dataset as you described above.  "stacked" is the name of the dataset that will be produced after you run the macro.

Regular Contributor
Posts: 161

Re: all column stacked in one

worked beautifully

thanks

Super User
Super User
Posts: 6,500

Re: all column stacked in one

data want ;

   set have (keep=c01)

         have (keep=c02 rename=(c02=c01))

         ....

         have (keep=c21 rename=(c21=c01))

  ;

run;

Super User
Super User
Posts: 6,500

Re: all column stacked in one

You can also use array and point= option on SET to re-read the same file multiple times.

Note that the STOP statement is key to prevent infinite looping of the data step.

data have ;

  input col1-col21 ;

cards;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

run;

data want ;

  array cols col1-col21 ;

  do i=1 to dim(cols);

    do p=1 to nobs ;

       set have point=p nobs=nobs;

       value = cols(i);

       output;

    end;

  end;

  keep value ;

  stop;

run;

Regular Contributor
Posts: 161

Re: all column stacked in one

I already have the dataset with 21 columns

Super User
Super User
Posts: 6,500

Re: all column stacked in one

Yes, but I and other readers don't have that dataset, so I created a simple one to use as an example..

The second data step is the one that will create the data you WANT from the data you HAVE.

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 992 views
  • 0 likes
  • 3 in conversation