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

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

1 ACCEPTED SOLUTION

Accepted Solutions
djbateman
Lapis Lazuli | Level 10

"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

9 REPLIES 9
djbateman
Lapis Lazuli | Level 10

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;

djbateman
Lapis Lazuli | Level 10

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();

R_A_G_
Calcite | Level 5

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

thanks

djbateman
Lapis Lazuli | Level 10

"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.

R_A_G_
Calcite | Level 5

worked beautifully

thanks

Tom
Super User Tom
Super User

data want ;

   set have (keep=c01)

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

         ....

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

  ;

run;

Tom
Super User Tom
Super User

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;

R_A_G_
Calcite | Level 5

I already have the dataset with 21 columns

Tom
Super User Tom
Super User

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 6298 views
  • 1 like
  • 3 in conversation