topic Re: Stacking data in SAS Programming

Stacking data

Danglytics — Wed, 25 Jul 2012 20:00:19 GMT

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!

Re: Stacking data

robby_beum — Wed, 25 Jul 2012 20:06:54 GMT

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;

Re: Stacking data

art297 — Wed, 25 Jul 2012 20:29:05 GMT

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;

Re: Stacking data

Linlin — Wed, 25 Jul 2012 20:40:33 GMT

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

Re: Stacking data

Linlin — Wed, 25 Jul 2012 20:48:44 GMT

Hi Art,

It is so good to see you!

can I define array this way?

array v(*) var:;

Thanks - Linlin

Re: Stacking data

tish — Wed, 25 Jul 2012 21:08:47 GMT

Yes, you can define an array with

array v {*} var:;

Re: Stacking data

Linlin — Wed, 25 Jul 2012 22:16:48 GMT

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;

Re: Stacking data

Haikuo — Wed, 25 Jul 2012 23:00:49 GMT

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

Re: Stacking data

tish — Thu, 26 Jul 2012 01:18:47 GMT

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

Re: Stacking data

Linlin — Thu, 26 Jul 2012 01:28:40 GMT

Thank you very much!!! what are the differences to define array before and after set statement?

Re: Stacking data

art297 — Thu, 26 Jul 2012 02:34:10 GMT

@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.

Re: Stacking data

Ksharp — Thu, 26 Jul 2012 04:54:12 GMT

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

Re: Stacking data

art297 — Thu, 26 Jul 2012 12:11:09 GMT

@Ksharp: Just talked with him yesterday .. quite alive and well!

Re: Stacking data

Danglytics — Thu, 26 Jul 2012 14:51:33 GMT

Thank you all for the help!