topic Stacking a single column in SAS Programming

Stacking a single column

jtrousd — Tue, 10 Jul 2012 12:21:37 GMT

Hey guys,

Ya'll have been so helpful! Here's another question I have. I have a data set with seven columns (variables). However, two of these columns represent the same variable, but they have different values. For instance:

Var1	Var2	Var3	Var4a	Var4b	Var5	Var6
1	a	y	1515	2323	orange	$500
2	b	n	2626	3434	blue	$600
3	c	y	3737	4545	green	$700
4	d	n	4848	5656	red	$800

Var4a and Var4b are the columns in question. All column attributes above correspond to the values in Var4a (ie, the values of 1, a, y, orange, and $500 correspond to 1515 in Var4a).

But I guess that's not too terribly important. What I'd like to do is take Var4a and stack it on top of Var4b, and get something like this:

Var1	Var2	Var3	Var4	Var5	Var6
1	a	y	1515	orange	$500
2	b	n	2626	blue	$600
3	c	y	3737	green	$700
4	d	n	4848	red	$800
.	.	.	2323	.	.
.	.	.	3434	.	.
.	.	.	4545	.	.
.	.	.	5656	.	.

Is there any simple way to do this, or is it some long convoluted mess of transposes and left joins and sorcery?

Thanks!

Re: Stacking a single column

Keith — Tue, 10 Jul 2012 12:46:36 GMT

Here's a simple way.

data have;

input var1 var2 $ var3 $ var4a var4b var5 $ var6;

cards;

1 a y 1515 2323 orange 500

2 b n 2626 3434 blue 600

3 c y 3737 4545 green 700

4 d n 4848 5656 red 800

;

run;

data want;

set have (drop=var4b rename=(var4a=var4))

have (keep=var4b rename=(var4b=var4));

run;

Re: Stacking a single column

jtrousd — Tue, 10 Jul 2012 12:52:21 GMT

You're awesome, Keith! Thanks!

Re: Stacking a single column

Haikuo — Tue, 10 Jul 2012 12:54:32 GMT

If you have some sort of group id to be considered, then the following code may help a little:

DATA have;

input (Var1 Var2 Var3 Var4a Var4b Var5 Var6) ($);

group=floor(_n_/4);

cards;

1 a y 1515 2323 orange $500

2 b n 2626 3434 blue $600

3 c y 3737 4545 green $700

4 d n 4848 5656 red $800

1 a y 1515 2323 orange $500

2 b n 2626 3434 blue $600

3 c y 3737 4545 green $700

4 d n 4848 5656 red $800

;

data want;

array vb (0:999) $ _temporary_;

set have;

by group;

if first.group then call missing(i);

i+1;

vb(i)=var4b;

output;

if last.group then do _n_=1 to i;

call missing (of _all_);

var4a=vb(_n_);

output;

end;

drop var4b i;

run;

proc print;RUN;

Regards,

Haikuo

Update: On my second thought, Keith's solution is superior even there is a group id:

data want;

set have (drop=var4b rename=(var4a=var4))

have (keep=group var4b rename=(var4b=var4));

by group;

run;