[Training Notes] Combining data in SAS

To get longer table(more rows): SET or (PROC APPEND)
To get wider table( more columns):
- when dataA and dataB are not with the same number of rows :
Some details
- match by BY
- SORT and then BY

To get longer table(more rows): SET or (PROC APPEND)

                                   dataA

                    Obs    ptid    ptname      ptgender

                      1    1001    Boris        Male   
                      2    1002    Drusilla     Female 
                      3    1003    Madhu        Female 
                      4    1004    Margaret     Female 
                      5    1005    Huseni       Male   
                      6    1006    Fergie       Female 
                      7    1007    Rekha        Female 
                      8    1008    Dasappa      Male   
                      9    1009    Sandhya      Female 
                     10    1010    Alley        Female 
 
                                                                           
 
                                   dataB

                    Obs    ptid    ptname     ptgender

                      1    1011    Monica      Female 
                      2    1012    Abel        Male   
                      3    1013    Christy     Female 
                      4    1014    Pushpa      Latha  
                      5    1015    Sunanda     Female 
                      6    1016    Scott       Male   
                      7    1017    Charlie     Male   
                      8    1018    Pradeep     Male   
                      9    1019    Umla        Naik   
                     10    1020    Selvi       Female 
 
                                                                           
 
---------------------------SET dataA dataB;--------------------------------

                    Obs    ptid    ptname      ptgender

                      1    1001    Boris        Male   
                      2    1002    Drusilla     Female 
                      3    1003    Madhu        Female 
                      4    1004    Margaret     Female 
                      5    1005    Huseni       Male   
                      6    1006    Fergie       Female 
                      7    1007    Rekha        Female 
                      8    1008    Dasappa      Male   
                      9    1009    Sandhya      Female 
                     10    1010    Alley        Female 
                     11    1011    Monica       Female 
                     12    1012    Abel         Male   
                     13    1013    Christy      Female 
                     14    1014    Pushpa       Latha  
                     15    1015    Sunanda      Female 
                     16    1016    Scott        Male   
                     17    1017    Charlie      Male   
                     18    1018    Pradeep      Male   
                     19    1019    Umla         Naik   
                     20    1020    Selvi        Female 
 
                                                                           
 
---------------------PROC APPEND base=dataA data=dataB;--------------------

                    Obs    ptid    ptname      ptgender

                      1    1001    Boris        Male   
                      2    1002    Drusilla     Female 
                      3    1003    Madhu        Female 
                      4    1004    Margaret     Female 
                      5    1005    Huseni       Male   
                      6    1006    Fergie       Female 
                      7    1007    Rekha        Female 
                      8    1008    Dasappa      Male   
                      9    1009    Sandhya      Female 
                     10    1010    Alley        Female 
                     11    1011    Monica       Female 
                     12    1012    Abel         Male   
                     13    1013    Christy      Female 
                     14    1014    Pushpa       Latha  
                     15    1015    Sunanda      Female 
                     16    1016    Scott        Male   
                     17    1017    Charlie      Male   
                     18    1018    Pradeep      Male   
                     19    1019    Umla         Naik   
                     20    1020    Selvi        Female

To get wider table( more columns):

                                   dataA

                    Obs    ptid    ptname      ptgender

                      1    1001    Boris        Male   
                      2    1002    Drusilla     Female 
                      3    1003    Madhu        Female 
                      4    1004    Margaret     Female 
                      5    1005    Huseni       Male   
                      6    1006    Fergie       Female 
                      7    1007    Rekha        Female 
                      8    1008    Dasappa      Male   
                      9    1009    Sandhya      Female 
                     10    1010    Alley        Female 
                     11    1011    Monica       Female 
                     12    1012    Abel         Male   
                     13    1013    Christy      Female 
                     14    1014    Pushpa       Female 
                     15    1015    Sunanda      Female 
                     16    1016    Scott        Male   
                     17    1017    Charlie      Male   
                     18    1018    Pradeep      Male   
                     19    1019    Naik         Male   
                     20    1020    Selvi        Female 
 
                                                                           
 
                                   dataB

                 Obs    pulse    bpsystolic    bpdiastolic

                   1      68         148            88    
                   2      80         120            70    
                   3      84         110            76    
                   4      80         110            62    
                   5      74         130            86    
                   6      74         130            86    
                   7      66         110            70    
                   8      72         120            80    
                   9      76         130            80    
                  10      70         110            70    
                  11      80         142            80    
                  12      80         112            82    
                  13      72         120            76    
                  14      80         120            74    
                  15      78         128            62    
                  16      82         120            80    
                  17      66         120            70    
                  18      78         130            80    
                  19      76         122            80    
                  20      72         112            64    
 
                                                                           
 
-------------------------------------MERGE dataA dataB;--------------------

 Obs    ptid    ptname      ptgender    pulse    bpsystolic    bpdiastolic

   1    1001    Boris        Male         68         148            88    
   2    1002    Drusilla     Female       80         120            70    
   3    1003    Madhu        Female       84         110            76    
   4    1004    Margaret     Female       80         110            62    
   5    1005    Huseni       Male         74         130            86    
   6    1006    Fergie       Female       74         130            86    
   7    1007    Rekha        Female       66         110            70    
   8    1008    Dasappa      Male         72         120            80    
   9    1009    Sandhya      Female       76         130            80    
  10    1010    Alley        Female       70         110            70    
  11    1011    Monica       Female       80         142            80    
  12    1012    Abel         Male         80         112            82    
  13    1013    Christy      Female       72         120            76    
  14    1014    Pushpa       Female       80         120            74    
  15    1015    Sunanda      Female       78         128            62    
  16    1016    Scott        Male         82         120            80    
  17    1017    Charlie      Male         66         120            70    
  18    1018    Pradeep      Male         78         130            80    
  19    1019    Naik         Male         76         122            80    
  20    1020    Selvi        Female       72         112            64

when dataA and dataB are not with the same number of rows :

------------------merging datasets not the same number of rows-------------

 Obs    ptid    ptname      ptgender    pulse    bpsystolic    bpdiastolic

   1    1001    Boris        Male         68         148            88    
   2    1002    Drusilla     Female       80         120            70    
   3    1003    Madhu        Female       84         110            76    
   4    1004    Margaret     Female       80         110            62    
   5    1005    Huseni       Male         74         130            86    
   6    1006    Fergie       Female       74         130            86    
   7    1007    Rekha        Female       66         110            70    
   8    1008    Dasappa      Male         72         120            80    
   9    1009    Sandhya      Female       76         130            80    
  10    1010    Alley        Female       70         110            70    
  11    1011    Monica       Female        .           .             .    
  12    1012    Abel         Male          .           .             .    
  13    1013    Christy      Female        .           .             .    
  14    1014    Pushpa       Female        .           .             .    
  15    1015    Sunanda      Female        .           .             .    
  16    1016    Scott        Male          .           .             .    
  17    1017    Charlie      Male          .           .             .    
  18    1018    Pradeep      Male          .           .             .    
  19    1019    Naik         Male          .           .             .    
  20    1020    Selvi        Female        .           .             .

Some details

match by BY

               Obs    ptid    ptname      ptage    ptgender

                 1    1001    Monica        32        F    
                 2    1002    Abel          38        M    
                 3    1003    Christy       35        F    
                 4    1004    Pushpa        33        F    
                 5    1005    Sunanda       31        F    
                 6    1006    Scott         39        M    
                 7    1007    Charlie       46        M    
                 8    1008    Pradeep       33        M    
                 9    1009    Naik          36        M    
                10    1010    Selvi         32        F    
                11    1011    Alley         36        F    
                12    1012    Boris         28        M    
                13    1013    Drusilla      33        F    
                14    1014    Madhu         31        F    
                15    1015    Margaret      31        F    
                16    1016    Huseni        44        M    
                17    1017    Fergie        35        F    
                18    1018    Rekha         18        F    
                19    1019    Dasappa       40        M    
                20    1020    Sandhya       28        F    
 
                                                                           
 
             Obs    ptid    pulse    bpsystolic    bpdiastolic

               1    1001      68         148            88    
               2    1002      80         120            70    
               3    1003      84         110            76    
               4    1004      80         110            62    
               5    1005      74         130            86    
               6    1006      74         130            86    
               7    1007      66         110            70    
               8    1008      72         120            80    
               9    1009      76         130            80    
              10    1010      70         110            70    
              11    1011      80         142            80    
              12    1012      80         112            82    
              13    1013      72         120            76    
              14    1014      80         120            74    
              15    1015      78         128            62    
              16    1016      82         120            80    
              17    1017      66         120            70    
              18    1018      78         130            80    
              19    1019      76         122            80    
              20    1020      72         112            64    
 
                                                                           
 
                       MERGE dataA dataB; BY (ptid);

Obs   ptid   ptname     ptage   ptgender   pulse   bpsystolic   bpdiastolic

  1   1001   Monica       32       F         68        148           88    
  2   1002   Abel         38       M         80        120           70    
  3   1003   Christy      35       F         84        110           76    
  4   1004   Pushpa       33       F         80        110           62    
  5   1005   Sunanda      31       F         74        130           86    
  6   1006   Scott        39       M         74        130           86    
  7   1007   Charlie      46       M         66        110           70    
  8   1008   Pradeep      33       M         72        120           80    
  9   1009   Naik         36       M         76        130           80    
 10   1010   Selvi        32       F         70        110           70    
 11   1011   Alley        36       F         80        142           80    
 12   1012   Boris        28       M         80        112           82    
 13   1013   Drusilla     33       F         72        120           76    
 14   1014   Madhu        31       F         80        120           74    
 15   1015   Margaret     31       F         78        128           62    
 16   1016   Huseni       44       M         82        120           80    
 17   1017   Fergie       35       F         66        120           70    
 18   1018   Rekha        18       F         78        130           80    
 19   1019   Dasappa      40       M         76        122           80    
 20   1020   Sandhya      28       F         72        112           64

SORT and then BY

data ptdetails;
input ptid ptname $ ptage ptgender $;
cards;
1001 Monica 32 F
1022 Abel 38 M
1003 Christy 35 F
1044 Pushpa 33 F
1005 Sunanda 31 F
1066 Scott 39 M
1007 Charlie 46 M
1088 Pradeep 33 M
1009 Naik 36 M
1110 Selvi 32 F
1011 Alley 36 F
1021 Boris 28 M
1013 Drusilla 33 F
1041 Madhu 31 F
1015 Margaret 31 F
1061 Huseni 44 M
1017 Fergie 35 F
1081 Rekha 18 F
1019 Dasappa 40 M
1020 Sandhya 28 F
run;

data clinicalexam;
input ptid pulse bpsystolic bpdiastolic;
cards;
1001 68 148 88
1002 80 120 70
1033 84 110 76
1004 80 110 62
1055 74 130 86
1006 74 130 86
1077 66 110 70
1008 72 120 80
1099 76 130 80
1010 70 110 70
1011 80 142 80
1012 80 112 82
1031 72 120 76
1014 80 120 74
1051 78 128 62
1016 82 120 80
1071 66 120 70
1018 78 130 80
1091 76 122 80
1020 72 112 64
run;

proc sort data=ptdetails;
by ptid;
run;

proc print data=ptdetails;
title "dataA";
run;

proc sort data=clinicalexam;
by ptid;
run;

proc print data=clinicalexam;
title "dataB";
run;

data ptnce;
merge ptdetails clinicalexam;
by ptid;
run;

proc print;
title "MERGE dataA dataB; BY (ptid);";
run;

data ptnce;
merge ptdetails(in=lily) clinicalexam(in=rose);
by ptid;
if lily then output;
run;

proc print;
title "output only data in dataA";
run;

data ptnce;
merge ptdetails(in=lily) clinicalexam(in=rose);
by ptid;
if rose then output;
run;

proc print;
title "output only data in dataB";
run;

data ptnce;
merge ptdetails(in=lily) clinicalexam(in=rose);
by ptid;
if lily and rose then output;
run;

proc print;
title "output data in both dataA and dataB";
run;

data ptnce;
merge ptdetails(in=lily) clinicalexam(in=rose);
by ptid;
if not lily or not rose then output;
run;

proc print;
title "output data in neither dataA nor dataB";
run;

                                   dataA

               Obs    ptid    ptname      ptage    ptgender

                 1    1001    Monica        32        F    
                 2    1003    Christy       35        F    
                 3    1005    Sunanda       31        F    
                 4    1007    Charlie       46        M    
                 5    1009    Naik          36        M    
                 6    1011    Alley         36        F    
                 7    1013    Drusilla      33        F    
                 8    1015    Margaret      31        F    
                 9    1017    Fergie        35        F    
                10    1019    Dasappa       40        M    
                11    1020    Sandhya       28        F    
                12    1021    Boris         28        M    
                13    1022    Abel          38        M    
                14    1041    Madhu         31        F    
                15    1044    Pushpa        33        F    
                16    1061    Huseni        44        M    
                17    1066    Scott         39        M    
                18    1081    Rekha         18        F    
                19    1088    Pradeep       33        M    
                20    1110    Selvi         32        F    
 
                                                                           
 
                                   dataB

             Obs    ptid    pulse    bpsystolic    bpdiastolic

               1    1001      68         148            88    
               2    1002      80         120            70    
               3    1004      80         110            62    
               4    1006      74         130            86    
               5    1008      72         120            80    
               6    1010      70         110            70    
               7    1011      80         142            80    
               8    1012      80         112            82    
               9    1014      80         120            74    
              10    1016      82         120            80    
              11    1018      78         130            80    
              12    1020      72         112            64    
              13    1031      72         120            76    
              14    1033      84         110            76    
              15    1051      78         128            62    
              16    1055      74         130            86    
              17    1071      66         120            70    
              18    1077      66         110            70    
              19    1091      76         122            80    
              20    1099      76         130            80    
 
                                                                           
 
                       MERGE dataA dataB; BY (ptid);

Obs   ptid   ptname     ptage   ptgender   pulse   bpsystolic   bpdiastolic

  1   1001   Monica       32       F         68        148           88    
  2   1002                 .                 80        120           70    
  3   1003   Christy      35       F          .          .            .    
  4   1004                 .                 80        110           62    
  5   1005   Sunanda      31       F          .          .            .    
  6   1006                 .                 74        130           86    
  7   1007   Charlie      46       M          .          .            .    
  8   1008                 .                 72        120           80    
  9   1009   Naik         36       M          .          .            .    
 10   1010                 .                 70        110           70    
 11   1011   Alley        36       F         80        142           80    
 12   1012                 .                 80        112           82    
 13   1013   Drusilla     33       F          .          .            .    
 14   1014                 .                 80        120           74    
 15   1015   Margaret     31       F          .          .            .    
 16   1016                 .                 82        120           80    
 17   1017   Fergie       35       F          .          .            .    
 18   1018                 .                 78        130           80    
 19   1019   Dasappa      40       M          .          .            .    
 20   1020   Sandhya      28       F         72        112           64    
 21   1021   Boris        28       M          .          .            .    
 22   1022   Abel         38       M          .          .            .    
 23   1031                 .                 72        120           76    
 24   1033                 .                 84        110           76    
 25   1041   Madhu        31       F          .          .            .    
 26   1044   Pushpa       33       F          .          .            .    
 27   1051                 .                 78        128           62    
 28   1055                 .                 74        130           86    
 29   1061   Huseni       44       M          .          .            .    
 30   1066   Scott        39       M          .          .            .    
 31   1071                 .                 66        120           70    
 32   1077                 .                 66        110           70    
 33   1081   Rekha        18       F          .          .            .    
 34   1088   Pradeep      33       M          .          .            .    
 35   1091                 .                 76        122           80    
 36   1099                 .                 76        130           80    
 37   1110   Selvi        32       F          .          .            .    
 
                                                                           
 
                         output only data in dataA

Obs   ptid   ptname     ptage   ptgender   pulse   bpsystolic   bpdiastolic

  1   1001   Monica       32       F         68        148           88    
  2   1003   Christy      35       F          .          .            .    
  3   1005   Sunanda      31       F          .          .            .    
  4   1007   Charlie      46       M          .          .            .    
  5   1009   Naik         36       M          .          .            .    
  6   1011   Alley        36       F         80        142           80    
  7   1013   Drusilla     33       F          .          .            .    
  8   1015   Margaret     31       F          .          .            .    
  9   1017   Fergie       35       F          .          .            .    
 10   1019   Dasappa      40       M          .          .            .    
 11   1020   Sandhya      28       F         72        112           64    
 12   1021   Boris        28       M          .          .            .    
 13   1022   Abel         38       M          .          .            .    
 14   1041   Madhu        31       F          .          .            .    
 15   1044   Pushpa       33       F          .          .            .    
 16   1061   Huseni       44       M          .          .            .    
 17   1066   Scott        39       M          .          .            .    
 18   1081   Rekha        18       F          .          .            .    
 19   1088   Pradeep      33       M          .          .            .    
 20   1110   Selvi        32       F          .          .            .    
 
                                                                           
 
                         output only data in dataB

Obs   ptid   ptname    ptage   ptgender   pulse   bpsystolic   bpdiastolic

  1   1001   Monica      32       F         68        148           88    
  2   1002                .                 80        120           70    
  3   1004                .                 80        110           62    
  4   1006                .                 74        130           86    
  5   1008                .                 72        120           80    
  6   1010                .                 70        110           70    
  7   1011   Alley       36       F         80        142           80    
  8   1012                .                 80        112           82    
  9   1014                .                 80        120           74    
 10   1016                .                 82        120           80    
 11   1018                .                 78        130           80    
 12   1020   Sandhya     28       F         72        112           64    
 13   1031                .                 72        120           76    
 14   1033                .                 84        110           76    
 15   1051                .                 78        128           62    
 16   1055                .                 74        130           86    
 17   1071                .                 66        120           70    
 18   1077                .                 66        110           70    
 19   1091                .                 76        122           80    
 20   1099                .                 76        130           80    
 
                                                                           
 
                    output data in both dataA and dataB

Obs   ptid   ptname    ptage   ptgender   pulse   bpsystolic   bpdiastolic

 1    1001   Monica      32       F         68        148           88    
 2    1011   Alley       36       F         80        142           80    
 3    1020   Sandhya     28       F         72        112           64    
 
                                                                           
 
                  output data in neither dataA nor dataB

Obs   ptid   ptname     ptage   ptgender   pulse   bpsystolic   bpdiastolic

  1   1002                 .                 80        120           70    
  2   1003   Christy      35       F          .          .            .    
  3   1004                 .                 80        110           62    
  4   1005   Sunanda      31       F          .          .            .    
  5   1006                 .                 74        130           86    
  6   1007   Charlie      46       M          .          .            .    
  7   1008                 .                 72        120           80    
  8   1009   Naik         36       M          .          .            .    
  9   1010                 .                 70        110           70    
 10   1012                 .                 80        112           82    
 11   1013   Drusilla     33       F          .          .            .    
 12   1014                 .                 80        120           74    
 13   1015   Margaret     31       F          .          .            .    
 14   1016                 .                 82        120           80    
 15   1017   Fergie       35       F          .          .            .    
 16   1018                 .                 78        130           80    
 17   1019   Dasappa      40       M          .          .            .    
 18   1021   Boris        28       M          .          .            .    
 19   1022   Abel         38       M          .          .            .    
 20   1031                 .                 72        120           76    
 21   1033                 .                 84        110           76    
 22   1041   Madhu        31       F          .          .            .    
 23   1044   Pushpa       33       F          .          .            .    
 24   1051                 .                 78        128           62    
 25   1055                 .                 74        130           86    
 26   1061   Huseni       44       M          .          .            .    
 27   1066   Scott        39       M          .          .            .    
 28   1071                 .                 66        120           70    
 29   1077                 .                 66        110           70    
 30   1081   Rekha        18       F          .          .            .    
 31   1088   Pradeep      33       M          .          .            .    
 32   1091                 .                 76        122           80    
 33   1099                 .                 76        130           80    
 34   1110   Selvi        32       F          .          .            .

[Training Notes] Combining data in SAS

Xue Zhang

2020/1/5

To get longer table(more rows): SET or (PROC APPEND)

To get wider table( more columns):

when dataA and dataB are not with the same number of rows :

Some details

match by BY

SORT and then BY