Update Query

[tmm]

I am doing an update query and trying to update amounts that are duped by ID, by date. The raw data looks like this

taxid           id               dos                   allowed

111            001           29nov10               100.00

123           001            29nov10               100.00

894           002            15nov10                 50.00

908          002             15nov10                 50.00

I want to do the update query so the results look like this

taxid           id               dos                   allowed

111            001           29nov10               100.00

123           001            29nov10                       0

894           002            15nov10                 50.00

908          002             15nov10                       0

Because 2 different taxids saw the patient it pulls the claim allowed amt 2 times. I need to keep the 2 taxid's in the table to show the patient was serviced by 2 different providers but i only need 1 of the allowed charges so the charges are not being over counted.

I thought something like:

proc sql;

update sum2

set last allowed = 0 where count(id) > 1 and dos=dos and allowed = allowed;

run;

[asishgautam]

how about this (untested):

proc sql noprint ;

     update sum2

     set allowed = 0

     where taxid in (select taxid

                           from (select id,max(taxid)

                                    from sum2

                                    group by id

                                   )

                           )                

     ;

quit ;

[Amir]

Hi,

If id is in order as shown in the example data then you could try:

data want;

  set have;

  by id;

  if not first.id then

    allowed=0;

run;

Regards,

Amir.

[tmm]

I noticed in the table there are allowed_amt's that are not = but there are 2 ID's

Can I do this:

data want;

set have;

by id;

if not first.id then allowed = 0 where allowed_amt = allowed_amt;

run;

[Ksharp]

If you want set the duplicated allowed to zero for the same id and same dos.

data have;
input taxid           id     $          dos  $                 allowed ;
cards;
111            001           29nov10               100.00
123           001            29nov10               100.00
894           002            15nov10                 50.00
908          002             15nov10                 50.00
;
run;
data want;
 set have;
 if id=lag(id) and dos=lag(dos) and allowed=lag(allowed) then allowed=0;
run;

Ksharp

[Amir]

Hi,

Ksharp has already provided an answer, but, for completeness, if you were looking for an answer using the by processing then the following should also work (with some extra data that has different allowed values for the same id):

data have;

  input taxid   $

        id      $

        dos     $

        allowed

  ;

  datalines;

111            001           29nov10               100.00

123           001            29nov10               100.00

894           002            15nov10                 50.00

908          002             15nov10                 50.00

909           003            14nov10                 50.00

910          003             14nov10                100.00

911           004            13nov10                 50.00

912          004             13nov10                 50.00

;

data want;

  set have;

  by id allowed;

  if not first.id and not(first.allowed and last.allowed) then

    allowed=0;

run;

The above code does not use the dos column.

Regards,

Amir.