DATA Step, Macro, Functions and more

how to arrange dataset variables according to other dataset values

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

how to arrange dataset variables according to other dataset values

Dear all,

 

today i came up with a question.

 

well, there are two datasets x & y, dataset x had values derived from some procedures and dataset y had random values, all i need is arrange dataset x variables according to values availed in dataset y.

i'll give you three tables to understand better.

 

Dataset X

Subject

hour

S1

S2

S3

1

-1

6

8

5

1

0

10

9

7

1

2

9

9

6

2

-1

5

7

10

2

0

9

6

8

2

2

8

9

8

   

Dataset Y

Subject

R1

R2

R3

1

3

1

2

2

1

3

2

 

now my expected output is

 

Output Dataset

Subject

hour

_1

_2

_3

1

-1

5

6

8

1

0

7

10

9

1

2

6

9

9

2

-1

5

10

7

2

0

9

8

6

2

2

8

8

9

 

dataset x variables are arranged using the values of dataset y and remember this arrangment varies from subject to subject. in the output dataset for subject 1, _1 variable values are taken from S3, _2 variable values are taken from S1, _3 variable values are taken from S2, belongs to dataset X, arranged with reference to values of dataset Y.

 

plz help me out.

 

thanks in advance.

Satish.


Accepted Solutions
Solution
‎07-11-2016 06:26 AM
Super User
Posts: 5,085

Re: how to arrange dataset variables according to other dataset values

I agree that arrays is the right tool for the job, but I think the program can be simplified.  Assuming your data sets are sorted by SUBJECT:

 

data want;

merge x y;

by subject;

array s {3};

array r {3};

array final {3} _1 _2 _3;

do _n_=1 to 3;

   final{_n_} = s{r{_n_}};

end;

* Keep/Drop variables as you see fit.;

run;

View solution in original post


All Replies
Super Contributor
Posts: 255

Re: how to arrange dataset variables according to other dataset values

Your problem is some what tricky. My solution requires the use of arrays.

 

If X and Y are pre-sorted by subject, then the following can be used. In the first DO-LOOP, R1 to R3 by subject is stored in an array(R). In the second DO-LOOP, variable-values of S1 to S3 are re-arranged using array values of R using array M.

data X;
input sub hr S1 S2 S3;
datalines;
1  -1  6 8  5
1   0 10 9  7
1   2  9 9  6
2  -1  5 7 10
2   0  9 6  8
2   2  8 9  8
;
run;


data Y;
input sub R1 R2 R3;
datalines;
1 3 1 2
2 1 3 2
;
run;

data want;
   do until(last.sub);
      set Y;
      by sub;
      array R[*] R1 - R3;
   end;
   do until(last.sub);
      set X;
      by sub;
      array m[*] _1 - _3;
      array k[*] S1 - S3;
      do i = 1 to dim(m);
         m[i] = k[R[i]];
      end;
      output;
   end;
keep sub hr _:;
run;


Another way is to store the data of Y into an array for both the subjects(when _N_ = 1) and re-arrange the variable-values of X as each observation is processed. This code does not require the pre-sorting of the data sets.

data want;
   array k[2, 3] _temporary_;
   if _N_ = 1 then do until(eof);
      set Y end = eof;
      array R[*] R1 - R3;
      do i = 1 to dim(R);
         k[sub, i] = R[i];
      end;
   end;
   set X;
   array m[*] _1 _2 _3;
   array S[*] S1 - S3;
   do i = 1 to dim(m);
      m[i] = S[k[sub, i]];
   end;
keep sub hr _:;
run;

 

 

Solution
‎07-11-2016 06:26 AM
Super User
Posts: 5,085

Re: how to arrange dataset variables according to other dataset values

I agree that arrays is the right tool for the job, but I think the program can be simplified.  Assuming your data sets are sorted by SUBJECT:

 

data want;

merge x y;

by subject;

array s {3};

array r {3};

array final {3} _1 _2 _3;

do _n_=1 to 3;

   final{_n_} = s{r{_n_}};

end;

* Keep/Drop variables as you see fit.;

run;

Contributor
Posts: 26

Re: how to arrange dataset variables according to other dataset values

Thanks to you guys, for your quick and every single reply. it helped me a lot.

thanks again.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 280 views
  • 2 likes
  • 3 in conversation