how to arrange dataset variables according to other dataset values

[satish123]

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.

[KachiM]

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;

[Astounding · Accepted solution]

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;

[satish123]

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

thanks again.