Help using Base SAS procedures

Forget how to Delete

Reply
Regular Contributor
Regular Contributor
Posts: 238

Forget how to Delete

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


Trusted Advisor
Posts: 1,630

Re: Forget how to Delete

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;

Respected Advisor
Posts: 3,124

Re: Forget how to Delete

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

Super Contributor
Posts: 1,040

Re: Forget how to Delete

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

Trusted Advisor
Posts: 1,630

Re: Forget how to Delete

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?

Super Contributor
Posts: 1,040

Re: Forget how to Delete

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

Respected Advisor
Posts: 3,124

Re: Forget how to Delete

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

Haikuo

Respected Advisor
Posts: 3,124

Re: Forget how to Delete

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

Ask a Question
Discussion stats
  • 7 replies
  • 204 views
  • 0 likes
  • 4 in conversation