Stacking data

[Danglytics]

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!

[robby_beum]

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;

[Haikuo]

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

[art297]

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;

[Linlin]

Hi Art,

It is so good to see you!

can I define array this way?

array v(*) var:;

Thanks - Linlin

[tish]

Yes, you can define an array with

array v {*} var:;

[Linlin]

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;

[tish]

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

[Linlin]

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

[art297]

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

[Ksharp]

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

[art297]

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

[Linlin · Accepted solution]

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

[Danglytics]

Thank you all for the help!