topic Re: all column stacked in one in SAS Procedures

all column stacked in one

R_A_G_ — Wed, 29 Jan 2014 15:51:27 GMT

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

Re: all column stacked in one

djbateman — Wed, 29 Jan 2014 15:55:29 GMT

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;

Re: all column stacked in one

Tom — Wed, 29 Jan 2014 15:55:47 GMT

data want ;

set have (keep=c01)

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

....

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

;

run;

Re: all column stacked in one

djbateman — Wed, 29 Jan 2014 15:58:27 GMT

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

Re: all column stacked in one

R_A_G_ — Wed, 29 Jan 2014 16:14:09 GMT

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

thanks

Re: all column stacked in one

djbateman — Wed, 29 Jan 2014 16:17:43 GMT

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

Re: all column stacked in one

Tom — Wed, 29 Jan 2014 16:20:02 GMT

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;

keep value ;

stop;

run;

Re: all column stacked in one

R_A_G_ — Wed, 29 Jan 2014 16:25:26 GMT

I already have the dataset with 21 columns

Re: all column stacked in one

R_A_G_ — Wed, 29 Jan 2014 16:28:32 GMT

worked beautifully

thanks

Re: all column stacked in one

Tom — Wed, 29 Jan 2014 16:33:47 GMT

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.