CODEWITHSUNDEEP
sas
ZeekDSA
ZeekDSA

Reputation: 86

SAS - issue in merging two datasets

I have the following table_1 :

TPMC    PWC PWSC    Site    ET  Date    Time    DIAM    PXMC    SF
7101    7101    US000521    1   Lathing 08Nov2016   11:58   890.3       1
7102    7102    US000361    1   Lathing 02Nov2016   13:01   878.1       1
7102    7102    UC000348    2   Lathing 07Nov2016   18:22   877.3       1
7106    7106    UC00424 1   Lathing 05Oct2016   9:43    890,4       1
7106    7106    UC00437 3   Lathing 07Nov2016   18:23   877.1       1
7106    7106    UC309   4   Lathing 07Nov2016   18:26   877.8       1
7107    7107    UC05327 1   Lathing 06Oct2016   8:41    837     1
7107    7107    UC200   2   Lathing 13Oct2016   12:53   890.55      1
7108    7108    UC000361    3   Lathing 02Nov2016   13:01   878.1       1
7108    7108    UC00432 1   Lathing 07Nov2016   18:25   877.8       1
7108    7108    UC106   2   Lathing 03Oct2016   9:37    890.3       1

and table_2 :

TPMC    PWC PWSC    Site    ET  Date    Time    DIAM    PXMC    SF
7101    .   .   .   .   01JAN16 .   .   .   .
7101    .   .   .   .   02JAN16 .   .   .   .
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
7101    .   .   .   .   30DEC16 .   .   .   .
7101    .   .   .   .   31DEC16 .   .   .   .
7102    .   .   .   .   01JAN16 .   .   .   .
7102    .   .   .   .   02JAN16 .   .   .   .
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
7102    .   .   .   .   30DEC16 .   .   .   .
7102    .   .   .   .   31DEC16 .   .   .   .

I want to merge two tables in a way that the output should look like something:

TPMC    PWC PWSC    Site    ET  Date    Time    DIAM    PXMC    SF
7101    .   .   .   .   01JAN16 .   .   .   .
7101    .   .   .   .   02JAN16 .   .   .   .
.   .   .   .   .   .   .   .   .   .
7101    7101    US000521    1   Lathing 08Nov2016   11:58   890.3       1
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
7101    .   .   .   .   30DEC16 .   .   .   .
7101    .   .   .   .   31DEC16 .   .   .   .
7102    .   .   .   .   01JAN16 .   .   .   .
7102    .   .   .   .   02JAN16 .   .   .   .
.   .   .   .   .   .   .   .   .   .
7102    7102    US000361    1   Lathing 02Nov2016   13:01   878.1       1
7102    7102    UC000348    2   Lathing 07Nov2016   18:22   877.3       1
.   .   .   .   .   .   .   .   .   .
.   .   .   .   .   .   .   .   .   .
7102    .   .   .   .   30DEC16 .   .   .   .
7102    .   .   .   .   31DEC16 .   .   .   .

How can it be done using 'Proc SQL' or 'Data Merge' or 'Combine'?

In the simplest form I used:

data data_set;
    set table_1 table_2;
run;

But this produced duplicate values of dates. For example:

TPMC    PWC ET  PWSC    Site    Date    Time    DIAM    PXMC    SF
7618    .   .   .   1   29SEP2016       .   .   .
7618    .   .   UC00424 2   30SEP2016       .   .   .
7618    .   Lathing UC00437 1   30SEP2016   17:15   890.500000  .   .
7618    .   Lathing UC309   2   30SEP2016   20:32   890.500000  .   .
7618    .   .   .   3   01OCT2016       .   .   .
7618    .   .   .   1   02OCT2016       .   .   .

I don't know how can I avoid this. I do not want rows where there is no 'ET' (i.e. ET is '.' or empty, I do not want those rows).

Also I want to learn other methods for future use.

Upvotes: 0

Views: 75

Answers (2)

heyydrien
heyydrien

Reputation: 981

One way to append 2 tables is using proc sql.

proc sql;
select t1.* from table1 t1
union all
select t2.* from table2 t2;
quit;

Make sure the 2 tables have exactly the same column names and are structured the same way. If both tables have the same records you'll end up with duplicate rows which you will need to filter.

Upvotes: 1

ZeekDSA
ZeekDSA

Reputation: 86

I will still stick with my answer as in above post...

data table2;
    set have001 have002;
run;

Let me see how would I resolve 'duplicate' data issues.

Upvotes: 0

Related Questions