Forget how to Delete

[tmm]

I have two tables. 1 that has everything and 1 that has 149 rows that need to be deleted from the table that has everything. Will call it table A and table B

I forget how to do I think a proc sort? To say delete these rows from Table A that are in Table B. The key in each is claim_nbr. Example data:

Table A has 112985k rows

claim_nbr            name           dos                    amt

111                    Doe              1/1/2010              0

112                    Doe              1/1/2011              5

113                    Doe              1/15/2010            4

114                   Doe               3/12/2011            0

Table B has 149 rows

claim_nbr          name              dos                  amt

111                   Doe               1/1/2010             0

114                   Doe               3/12/2011           0

[PaigeMiller]

Assuming the data is sorted, I would just do a merge.

Something like this

data c;

     merge a(in=in1) b(in=in2);

     by claim_nbr;

     if in2 then delete;

run;

[Haikuo]

data a;

input (claim_nbr            name) (:$)  dos:mmddyy10.   amt;

cards;

111                    Doe              1/1/2010              0

112                    Doe              1/1/2011              5

113                    Doe              1/15/2010            4

114                   Doe               3/12/2011            0

;

data b;

input (claim_nbr            name) (:$)  dos:mmddyy10.   amt;

cards;

111                   Doe               1/1/2010             0

114                   Doe               3/12/2011           0

;

/*If presorted by claim_nbr*/

data want;

merge a(in=a) b(in=b);

   by claim_nbr;

   if a and ^b;

run;

/*you can choose to use SQL if not presorted*/

proc sql noprint;

  create table want as

     select * from a

      except

      select * from

     b;

quit;

Haikuo

[robertrao]

Hi ,

In this example its fine because each table has unique observation. What if either of the tables have duplicates by claim_no and differeing dates may be within the same claim_ no??

Thnaks

[PaigeMiller]

What if either of the tables have duplicates by claim_no and differeing dates may be within the same claim_ no??

This is an incomplete problem specification, in my opinion, so I can't answer unless you give us more information.

So we have duplicates and different dates ... then what is the desired outcome?

[robertrao]

Yes

So we have duplicates with claim _no with differing dates(different dates)

Table A has 112985k rows

claim_nbr            name           dos                    amt

Ok .If its like the one shown below we merge it by claim_no and dos. Then it should work fine??

Is that right??

Thanks

111 Doe 1/1/2010 0

112                    Doe              1/1/2011              5

112                    Doe              5/1/2011              5

112                    Doe              8/1/2011              5

113                    Doe              1/15/2010            4

114                   Doe               3/12/2011            0

Table B has 149 rows

claim_nbr          name              dos                  amt

112                   Doe               1/1/2010             0

112                    Doe              5/1/2011              5

114                   Doe               3/12/2011           0

[Haikuo]

Yes, if you want to keep those records. It is all about keys, and how you define your keys.

Haikuo

[Haikuo]

Yes, the SQL solution above is not equivalent to data step one, if this is what you are asking. it will only remove identical records, not by keys. Here is a one SQL based on keys:

proc sql noprint;

  create table want as

     select * from a

      where claim_nbr not in ( select claim_nbr from

     b) ;

quit;

Haikuo