Stacking a single column

[jtrousd]

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!

[Keith · Accepted solution]

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;

[jtrousd]

You're awesome, Keith! Thanks!

[Haikuo]

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;