Hi,
I have a dataset that looks like:
id q1f_concept q1f_1 q1f_2 q1f_3 q1s_concept q1s_1 q1s_2 q1s_3
1 1 5 4 3 2 3 4 5
2 2 4 4 4 1 3 3 3
etc.
I'd like to stack my q1f and q1s together so it would look like:
id q1_concept q1_1 q1_2 q1_3
1 1 5 4 3
1 2 3 4 5
2 2 4 4 4
2 1 3 3 3
etc.
I'm not sure what the best/simplest way to do it is, but I believe a single data step using arrays would do the trick.
Thanks for your help!
a single data step solution:
data have;
input id q1f_concept q1f_1 q1f_2 q1f_3 q1s_concept q1s_1 q1s_2 q1s_3 ;
cards;
1 1 5 4 3 2 3 4 5
2 2 4 4 4 1 3 3 3
;
data want(keep=id q1_concept q1_1-q1_3);
set have;
array _f{*} q1f:;
array _s{*} q1s:;
array _q{*} q1_concept q1_1-q1_3;
do i=1 to dim(_f);
_q(i)=_f(i);
end;
output;
do i=1 to dim(_s);
_q(i)=_s(i);
end;
output;
run;
proc print;run;
Obs id q1_concept q1_1 q1_2 q1_3
1 1 1 5 4 3
2 1 2 3 4 5
3 2 2 4 4 4
4 2 1 3 3 3
Untested for sure...how about something like this?
data want1(keep=id q1f_concept q1f_1 q1f_2 q1f_3)
want2(keep=id q1s_concept q1s_1 q1s_2 q1s_3);
set have;
output want1;
output want2;
run;
data want3;
set want1
want2;
run;
how about:
data want;
set have (keep=id q1f_concept q1f_1 q1f_2 q1f_3 rename=(q1f_concept=q11 q1f_1=q12 q1f_2=q13 q1f_3=q14))
have (keep=id q1s_concept q1s_1 q1s_2 q1s_3 rename=(q1s_concept=q11 q1s_1=q12 q1s_2=q13 q1s_3=q14));
by id;
run;
Haikuo
Not sure why you would want to do what you ask and, unless you don't have to differentiate between the types, you ought to add a field that differentiates between them. e.g.:
data want (drop=q1f: q1s:);
set have;
type='f';
q1_concept=q1f_concept;
q1_1=q1f_1;
q1_2=q1f_2;
q1_3=q1f_3;
output;
type='s';
q1_concept=q1s_concept;
q1_1=q1s_1;
q1_2=q1s_2;
q1_3=q1s_3;
output;
run;
Hi Art,
It is so good to see you!
can I define array this way?
array v(*) var:;
Thanks - Linlin
Yes, you can define an array with
array v {*} var:;
Hi tish,
Thank you for your reply.
It doesn't work in my code.
data have;
input id q1f_concept q1f_1 q1f_2 q1f_3 q1s_concept q1s_1 q1s_2 q1s_3 ;
cards;
1 1 5 4 3 2 3 4 5
2 2 4 4 4 1 3 3 3
;
data want(keep=id q1_concept q1_1 q1_2 q1_3);
retain id q1_concept q1_1 q1_2 q1_3;
array _f(*) /*q1f_concept q1f_1 q1f_2 q1f_3*/ q1f:;
array _s(*) /* q1s_concept q1s_1 q1s_2 q1s_3*/ q1s:;
array _q(*) q1_concept q1_1 q1_2 q1_3;
set have;
do i=1 to dim(_f);
_q(i)=_f(i);
end;
output;
do i=1 to dim(_s);
_q(i)=_s(i);
end;
output;
run;
Hi Linlin. I use this construction a lot when defining arrays. It works for me, but I have always used it after a set statement, not before. Tish
Thank you very much!!! what are the differences to define array before and after set statement?
@Linlin: Program Data Vector
The variables from the first file don't get to populate the program data vector until after the set statement. Add some putlog _all_; statements at various points in your code and you can see what is happening in the background.
Art.T
Long time no see.
Did you contact FriedEgg recently ?
There is a gunshot happened at Denver City . Many people lost their life . He is OK ?
Ksharp
@Ksharp: Just talked with him yesterday .. quite alive and well!
a single data step solution:
data have;
input id q1f_concept q1f_1 q1f_2 q1f_3 q1s_concept q1s_1 q1s_2 q1s_3 ;
cards;
1 1 5 4 3 2 3 4 5
2 2 4 4 4 1 3 3 3
;
data want(keep=id q1_concept q1_1-q1_3);
set have;
array _f{*} q1f:;
array _s{*} q1s:;
array _q{*} q1_concept q1_1-q1_3;
do i=1 to dim(_f);
_q(i)=_f(i);
end;
output;
do i=1 to dim(_s);
_q(i)=_s(i);
end;
output;
run;
proc print;run;
Obs id q1_concept q1_1 q1_2 q1_3
1 1 1 5 4 3
2 1 2 3 4 5
3 2 2 4 4 4
4 2 1 3 3 3
Thank you all for the help!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.