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!
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;
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;
You're awesome, Keith! Thanks!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.