Merge 2 tables where I want to append only the records in the second table that are not in 1st table

DHS_SASADM — Fri, 10 Feb 2017 16:25:42 GMT

I have two tables of six columns. Both can contain multiple occurrances of a record. I want to append to table one the records from table two where the records from table two are not in table 1.

Thanks for any help on this in advance.

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t

ballardw — Fri, 10 Feb 2017 16:31:35 GMT

By "multiple occurences of record" do you mean completely duplicated row of data or duplication of some identification information?

It helps to post brief examples of your data and the desired results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t

rogerjdeangelis — Fri, 10 Feb 2017 23:50:53 GMT

Table 1 plus new records from table 2

I have two tables of six columns. Both can contain multiple occurrances of a record.
I want to append to table one the records from table two where the records
from table two are not in table 1.

inspired by
https://goo.gl/555Ghw
https://communities.sas.com/t5/Base-SAS-Programming/Merge-2-tables-where-I-want-to-append-only-the-records-in-the/m-p/331586


HAVE  datasets have1 and have 2
================================;;;;/*'*/ *);*};*];*/;/*"*/;%mend;run;quit;%end;end;run;endcomp;%utlfix;

Up to 40 obs from have1 total obs=18

Obs    NAME        SEX             AGE          HEIGHT          WEIGHT

  1    Henry        M               14            63.5           102.5
  2    Henry        M               14            63.5           102.5
  3    James        M               12            57.3              83
  4    Jane         F               12            59.8            84.5
  5    Jane         F               12            59.8            84.5
  6    Janet        F               15            62.5           112.5
  7    Jeffrey      M               13            62.5              84
  8    John         M               12              59            99.5
  9    Joyce        F               11            51.3            50.5
 10    Joyce        F               11            51.3            50.5
 11    Judy         F               14            64.3              90
 12    Judy         F               14            64.3              90
 13    Louise       F               12            56.3              77
 14    Louise       F               12            56.3              77
 15    Mary         F               15            66.5             112
 16    Mary         F               15            66.5             112
 17    Philip       M               16              72             150
 18    Philip       M               16              72             150



Up to 40 obs WORK.HAVE2 total obs=14

Obs    NAME        SEX             AGE          HEIGHT          WEIGHT

  1    John         M               12              59            99.5
  2    Joyce        F               11            51.3            50.5
  3    Joyce        F               11            51.3            50.5
  4    Judy         F               14            64.3              90
  5    Louise       F               12            56.3              77
  6    Mary         F               15            66.5             112
  7    Philip       M               16              72             150
  8    Philip       M               16              72             150
  9    Robert       M               12            64.8             128
 10    Ronald       M               15              67             133
 11    Thomas       M               11            57.5              85
 12    Thomas       M               11            57.5              85
 13    William      M               15            66.5             112
 14    William      M               15            66.5             112


WANT
====

Obs    SRC      NAME        SEX    AGE          HEIGHT          WEIGHT

  1    have1    Henry        M      14            63.5           102.5 Keep all these
  2    have1    Henry        M      14            63.5           102.5 even the dups
  3    have1    James        M      12            57.3              83
  4    have1    Jane         F      12            59.8            84.5
  5    have1    Jane         F      12            59.8            84.5
  6    have1    Janet        F      15            62.5           112.5
  7    have1    Jeffrey      M      13            62.5              84
  8    have1    John         M      12              59            99.5
  9    have1    Joyce        F      11            51.3            50.5
 10    have1    Joyce        F      11            51.3            50.5
 11    have1    Judy         F      14            64.3              90
 12    have1    Judy         F      14            64.3              90
 13    have1    Louise       F      12            56.3              77
 14    have1    Louise       F      12            56.3              77
 15    have1    Mary         F      15            66.5             112
 16    have1    Mary         F      15            66.5             112
 17    have1    Philip       M      16              72             150
 18    have1    Philip       M      16              72             150

 19    have2    John         M      12              59            99.5  These are not in
 20    have2    Joyce        F      11            51.3            50.5  have1
 21    have2    Judy         F      14            64.3              90
 22    have2    Louise       F      12            56.3              77
 23    have2    Mary         F      15            66.5             112
 24    have2    Philip       M      16              72             150
 25    have2    Robert       M      12            64.8             128
 26    have2    Ronald       M      15              67             133
 27    have2    Thomas       M      11            57.5              85
 28    have2    William      M      15            66.5             112


WORKING CODE

         union
            corr

FULL SOLUTION
=============

* create some data;
data have1 have2;
  set sashelp.class;
  if 5 <= _n_ < =15 then do;
    if uniform(5731)<.5 then do;
       output have1;
       output have1;
    end;
    else output have1;
  end;
  if 10 <= _n_ < =20 then do;
    if uniform(5731)<.5 then do;
       output have2;
       output have2;
    end;
    else output have2;
  end;
run;quit;

* have2 not in have1;
proc sql;
  create
     table wantpre(where=(src='have2')) as
  select
     'have1' as src
     ,*
  from
     have1
  union
     corr
  select
     'have2' as src
     ,*
  from
      have2
;quit;

data want;
  retain src;
  set have1(in=one) wantpre;
  if one then src='have1';
run;quit;

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t

art297 — Sat, 11 Feb 2017 00:10:52 GMT

Since both files contain multiple records for an ID, you would have to let us know which variables define the ones you don't want added from the 2nd data set.

Art, CEO, AnalystFinder.com

topic Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t in SAS Programming

Merge 2 tables where I want to append only the records in the second table that are not in 1st table

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t

Re: Merge 2 tables where I want to append only the records in the second table that are not in 1st t