topic Re: Update Query in SAS Procedures

Update Query

tmm — Fri, 20 Jul 2012 14:46:19 GMT

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;

Re: Update Query

asishgautam — Fri, 20 Jul 2012 15:39:55 GMT

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 ;

Re: Update Query

Amir — Fri, 20 Jul 2012 17:46:55 GMT

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.

Re: Update Query

tmm — Sun, 22 Jul 2012 16:06:10 GMT

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;

Re: Update Query

Ksharp — Mon, 23 Jul 2012 04:53:00 GMT

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

Re: Update Query

Amir — Mon, 23 Jul 2012 13:15:52 GMT

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.