If state in data step

[tmm]

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.

[Haikuo · Accepted solution]

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]

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.