BookmarkSubscribeRSS Feed
HoangNg
Calcite | Level 5

hello friends, I was stuck on the last part to merge two table. can somebody help me with this.

Patients underwent knee replacement surgeries for one or both knees. The data record patient id, the replaced knee number (1 or 2), and satisfaction scores pre-operatively, one day, one week, and one month after the surgery. The data are

 
 

Patient         Knee       Score     Score at     Score at     Score at

      ID       Number   Pre-op   one day     one week   one month

    01           1               0              5                7                 10

    02           1               0            10              15                 15

    02           2               3              5                8                 10

    03           1               0              3                3                   3

    03           2               0              6                9                   9

    04           1               0              4              10                 10

 

 

(a) Use the ARRAY statement to create a data set containing satisfaction scores for knee 1. The data set should look like this                

                                      id    visit    score

 

                                      01      1         0

                                      01      2         5

                                      01      3         7

                                      01      4        10

                                      02      1         0

                                      02      2        10

                                      02      3        15

                                      02      4        15

                                      03      1         0

                                      03      2         3

                                      03      3         3

                                      03      4         3

                                      04      1         0

                                      04      2         4

                                      04      3        10

                                      04      4        10

 

 (b) Use the ARRAY statement to create a data set containing satisfaction scores for knee 2. The data set should look like this                         

                                      id    visit    score

 

                                      02      1         3

                                      02      2         5

                                      02      3         8

                                      02      4        10

                                      03      1         0

                                      03      2         6

                                      03      3         9

                                      03      4         9

 

(c) Use the MERGE statement to create the merged file that looks like this

 

                                                score_    score_

                               id     visit      knee1     knee2

 

                               01    pre_op        0         .

                               01    day 1         5         .

                               01    week 1        7         .

                               01    month 1      10         .

                               02    pre_op        0         3

                               02    day 1        10         5

                               02    week 1       15         8

                               02    month 1      15        10

                               03    pre_op        0         0

                               03    day 1         3         6

                               03    week 1        3         9

                               03    month 1       3         9

                               04    pre_op        0         .

                               04    day 1         4         .

                               04    week 1       10         .

                               04    month 1      10         .

1 REPLY 1
Reeza
Super User

Same as previous question - please post what you've tried so far and ask more specific questions. As posted, it seems like you're asking someone to do your homework for you. 

Things you can look into:

 

SET statement. 

 

This doesn't look like a merge, it looks like a transpose, from wide to long using an Array. 

 

Here is a transposing data tutorial:


Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/


@HoangNg wrote:

hello friends, I was stuck on the last part to merge two table. can somebody help me with this.

Patients underwent knee replacement surgeries for one or both knees. The data record patient id, the replaced knee number (1 or 2), and satisfaction scores pre-operatively, one day, one week, and one month after the surgery. The data are

 
 

Patient         Knee       Score     Score at     Score at     Score at

      ID       Number   Pre-op   one day     one week   one month

    01           1               0              5                7                 10

    02           1               0            10              15                 15

    02           2               3              5                8                 10

    03           1               0              3                3                   3

    03           2               0              6                9                   9

    04           1               0              4              10                 10

 

 

(a) Use the ARRAY statement to create a data set containing satisfaction scores for knee 1. The data set should look like this                

                                      id    visit    score

 

                                      01      1         0

                                      01      2         5

                                      01      3         7

                                      01      4        10

                                      02      1         0

                                      02      2        10

                                      02      3        15

                                      02      4        15

                                      03      1         0

                                      03      2         3

                                      03      3         3

                                      03      4         3

                                      04      1         0

                                      04      2         4

                                      04      3        10

                                      04      4        10

 

 (b) Use the ARRAY statement to create a data set containing satisfaction scores for knee 2. The data set should look like this                         

                                      id    visit    score

 

                                      02      1         3

                                      02      2         5

                                      02      3         8

                                      02      4        10

                                      03      1         0

                                      03      2         6

                                      03      3         9

                                      03      4         9

 

(c) Use the MERGE statement to create the merged file that looks like this

 

                                                score_    score_

                               id     visit      knee1     knee2

 

                               01    pre_op        0         .

                               01    day 1         5         .

                               01    week 1        7         .

                               01    month 1      10         .

                               02    pre_op        0         3

                               02    day 1        10         5

                               02    week 1       15         8

                               02    month 1      15        10

                               03    pre_op        0         0

                               03    day 1         3         6

                               03    week 1        3         9

                               03    month 1       3         9

                               04    pre_op        0         .

                               04    day 1         4         .

                               04    week 1       10         .

                               04    month 1      10         .





sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 478 views
  • 0 likes
  • 2 in conversation