BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

I know my code is off but this is the closet I can think or put together.

DATA ReAdm.Sum2; SET ReAdm.Sum2_Clean;

IF COUNT(MBR_SYS_ID) >2 AND READMIT_DT >2 AND ALLOWED_AMT = ALLOWED_AMT THEN DELETE DUPE RECORD;

RUN;

The goal is to say any mbr_sys_id I have that is more than 2 of them as long as they have a readmit_dt that is equal and an allowed_amt that is equal, delete all but 1 of the records. An example of the duplicate data I have is as follows:

who               market                   mbr_sys_id          readmit_dt                 allowed_amt

111               long island                 2020                 29nov10                     25324.00

222               long island                 2020                 29nov10                     25324.00

333               maine                        1010                15aug10                      8350.00

444              new york                    1010                 17may11                     550.00

555              california                    3030                 16sep11                      250.00

782              california                    3030                 16sep11                      250.00

111              san diego                   4040                 17dec10                     750.00

222              san diego                   4040                 17dec10                    1900.00

The who is our tax id for the doctor and even though 2 different doctors attended the mbr, the claim overall amount is equal and I do not want to charge against the patient 2 times if that amount is the same. So I am trying to create a query to get rid of any dupe records where the mbr_sys_id is in 2 or more times with a readmit_dt that is the same and in 2 or more times and the allowed_amt is equal.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

If I understand you correctly, use Proc sort nodupkey seems to do the job:

proc sort data=have our=want nodupkey;

by mbr_sys_id  readmit_dt  allowed_amt;

run;

Haikuo

View solution in original post

2 REPLIES 2
Haikuo
Onyx | Level 15

If I understand you correctly, use Proc sort nodupkey seems to do the job:

proc sort data=have our=want nodupkey;

by mbr_sys_id  readmit_dt  allowed_amt;

run;

Haikuo

art297
Opal | Level 21

You have to provide more info.  Which who do you want to keep?  Which market to you want to keep?

It looks like you could do it easily with either a do loop.  You could include the relevant variables in a by statement and know if there are multiple records simply by looking to see if a record is both first and last in a group.

From your pseudo code I would assume that you would use mbr_sys_id, readmit_dt and allowed_amt in the by statement.

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
  • 2 replies
  • 624 views
  • 0 likes
  • 3 in conversation