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
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;
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
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
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?
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
Yes, if you want to keep those records. It is all about keys, and how you define your keys.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.