Solved
Contributor
Posts: 26

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

Satish.

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

## 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;

All Replies
Super Contributor
Posts: 326

## 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: 6,785

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