BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
satish123
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
KachiM
Rhodochrosite | Level 12

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
PROC Star

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
Fluorite | Level 6

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

thanks again.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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