BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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


7 REPLIES 7
PaigeMiller
Diamond | Level 26

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;

--
Paige Miller
Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Diamond | Level 26

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?

--
Paige Miller
robertrao
Quartz | Level 8

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
Onyx | Level 15

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

Haikuo

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 795 views
  • 0 likes
  • 4 in conversation