DATA Step, Macro, Functions and more

Stacking a single column

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Stacking a single column

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:

Var1Var2Var3Var4aVar4bVar5Var6

1

ay15152323orange$500
2bn26263434blue$600
3cy37374545green$700
4dn48485656red$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:

Var1Var2Var3Var4Var5Var6
1ay1515orange$500
2bn2626blue$600
3cy3737green$700
4dn4848red$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!


Accepted Solutions
Solution
‎07-10-2012 08:46 AM
Regular Contributor
Posts: 151

Re: Stacking a single column

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;

View solution in original post


All Replies
Solution
‎07-10-2012 08:46 AM
Regular Contributor
Posts: 151

Re: Stacking a single column

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;

Contributor
Posts: 33

Re: Stacking a single column

You're awesome, Keith! Thanks!

Respected Advisor
Posts: 3,156

Re: Stacking a single column

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 192 views
  • 3 likes
  • 3 in conversation