DATA Step, Macro, Functions and more

Stacking data

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Stacking data

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!


Accepted Solutions
Solution
‎07-25-2012 04:40 PM
Super Contributor
Posts: 1,636

Re: Stacking data

Posted in reply to Danglytics

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

View solution in original post


All Replies
Contributor
Posts: 73

Re: Stacking data

Posted in reply to Danglytics

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;

Respected Advisor
Posts: 3,156

Re: Stacking data

Posted in reply to robby_beum

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

PROC Star
Posts: 7,468

Re: Stacking data

Posted in reply to Danglytics

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: q1sSmiley Happy;

  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;

Super Contributor
Posts: 1,636

Re: Stacking data

Hi Art,

It is so good to see youSmiley Happy!

can I define array this way?

array v(*) var:;

Thanks - Linlin

Contributor
Posts: 52

Re: Stacking data

Yes, you can define an array with

array v {*} var:;

Super Contributor
Posts: 1,636

Re: Stacking data

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;

Contributor
Posts: 52

Re: Stacking data

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

Super Contributor
Posts: 1,636

Re: Stacking data

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

PROC Star
Posts: 7,468

Re: Stacking data

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

Super User
Posts: 10,020

Re: Stacking data

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

PROC Star
Posts: 7,468

Re: Stacking data

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

Solution
‎07-25-2012 04:40 PM
Super Contributor
Posts: 1,636

Re: Stacking data

Posted in reply to Danglytics

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

Contributor
Posts: 66

Re: Stacking data

Thank you all for the help!

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 385 views
  • 1 like
  • 7 in conversation