topic Forget how to Delete in SAS Procedures

Forget how to Delete

tmm — Fri, 05 Oct 2012 12:46:36 GMT

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

Re: Forget how to Delete

PaigeMiller — Fri, 05 Oct 2012 12:55:43 GMT

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;

Re: Forget how to Delete

Haikuo — Fri, 05 Oct 2012 12:58:08 GMT

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

quit;

Haikuo

Re: Forget how to Delete

robertrao — Fri, 05 Oct 2012 14:12:04 GMT

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

Re: Forget how to Delete

PaigeMiller — Fri, 05 Oct 2012 15:22:30 GMT

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?

Re: Forget how to Delete

Haikuo — Fri, 05 Oct 2012 15:46:02 GMT

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

Re: Forget how to Delete

robertrao — Fri, 05 Oct 2012 15:53:40 GMT

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

Re: Forget how to Delete

Haikuo — Fri, 05 Oct 2012 16:07:58 GMT

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

Haikuo