BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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;

5 REPLIES 5
asishgautam
Calcite | Level 5

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
PROC Star

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
Fluorite | Level 6 tmm
Fluorite | Level 6

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
Super User

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
PROC Star

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.

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
  • 5 replies
  • 882 views
  • 1 like
  • 4 in conversation