DATA Step, Macro, Functions and more

Rate Application to Claims Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Rate Application to Claims Data

[ Edited ]

Greetings all.  Thank you so much for your previous assistance on this forum.  I perused the exisiting messages for this issue and I have a feeling there is an answer; however, I'm must not be using the correct key words.  So, I'm going to put this out there.

 

I have a set of claims data.  Line item data.  I have created a rate specific to a service line.  For example, a radiology procedure will pay a certain dollar amount and I have also created a flag to denote that what I applied to that particular line item.  No problem assigning the line item rate.  The problem I am running into is when there are other line items that take precendece and would essentially cause this radiology line to not pay (be zero dollars).  I have tried a series of first., last., do until, and I just can't seem to get the english into code.

 

Data Have:

Key     Line_No     Flag     Rate

3217    1                 29      102.81

3217    2                 29      145.78

3217    3                   1        2290

3217    4                  23      6942

3217    5                    8        2418

3218    1                  27      1234

3218    2                  28      567

3218    3                  29      890

 

The data above is what I might consider a first pass through all of the service lines.  Now I need to loop through the claims to determine what lines need to receive a zero rate when the a key for any of the lines of a particular claim (3217) is in (1,2,8).

 

Data Want:

Key     Line_No     Flag     Rate

3217    1                 29       0

3217    2                 29       0

3217    3                   1       2290

3217    4                  23      0

3217    5                    8      2418

3218    1                  27      1234

3218    2                  28      567

3218    3                  29      890

 

Flags of 1,2,8 take precedent over the other lines and are the only lines that will pay. With KEY 3218, you can see there aren't any flags of 1,2,or 8 and the payment is valid.

 

I'm sure there is something really obvious here, but I've stared at it so long, I'm not seeing it.

 

Any assistance or direction is greatly appreciated.

 

Brian

 


Accepted Solutions
Solution
‎10-13-2016 08:52 AM
Respected Advisor
Posts: 3,902

Re: Rate Application to Claims Data

[ Edited ]

 

Below two coding options.

Option 1 requires your source data to be pre-sorted by key, Option 2 doesn't require any sorting but requires more memory.

data have;
  infile datalines truncover;
  input Key Line_No Flag Rate;
datalines;
3217 1 29 102.81
3217 2 29 145.78
3217 3 1 2290
3217 4 23 6942
3217 5 8 2418
3218 1 27 1234
3218 2 28 567
3218 3 29 890
;
run;

/* option 1 */
data want1(drop=_:);

  _set_zero_flg='0';
  do until(last.key);
    set have(keep=key flag);
    by key;
    if Flag in (1,2,8) then _set_zero_flg='1';
  end;

  do until(last.key);
    set have;
    by key;
    if _set_zero_flg='1' and Flag not in (1,2,8) then Rate=0;
    output;
  end;

run;

/* option 2 */
data want2;
  set have;

  if _n_=1 then
    do;
      dcl hash h1 (dataset:'have(keep=key Flag where=(Flag in (1,2,8)))',multidata:'n');
      h1.defineKey('key');
      h1.defineDone();
    end;

  if h1.check()=0 and Flag not in (1,2,8) then Rate=0;
run;

 

View solution in original post


All Replies
Super User
Posts: 6,972

Re: Rate Application to Claims Data

The first thing to do is proper formatting of code. Spaghetti coding always prevents visual detection of semantical errors.

data op_data3b;
do until (last.key);
  set op_data3;
  by key;
  if flag not in (1 2 8)
  then do;
    rad = put(hcpcs,$rad_wt.);
    if rad ne 1
    then do;
      rate=rad * units;
      flag=29;
    end;
    else if flag in (1 2 8)
    then do;
      rate=0;
      flag=29;
    end;
  end;
end;
run;

Now you will see that the code for flag in (1,2,8) is INSIDE the code for flag not in (1,2,8), so that condition can never be true.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Rate Application to Claims Data

Kurt,

 

Thank you for your quick response.  It probably would have helped if I had taken the time to post this correctly.  Copy and paste are not always your friend.  lol

 

I took a break from this (again) and had a little clarity.  I do see that I have the IN statement contained within the NOT statment.  Thank you for pointing that out.  I realized my "do until (last.key)" was only picking up the last key (as it is supposed to do).  I also realized I needed to utilize the line number (which I did not provide to everyone in my DATA HAVE statment).  I'm testing this theory at the moment:

 

do _n_ = 1 by 1 until (max(ub_line));

   if flag not in (1 2 8) then do;

   rad = put(hcpcs,$rad_wt.);

      if rad ne 1 then do;

       rate=rad * units;

       flag=27;

      end;

   end;

end;

 

Super User
Posts: 6,972

Re: Rate Application to Claims Data

To get proper formatting of SAS code (and "prettying up" with coloring like the enhanced editor does), use the "little running man" icon on top of your post window to get a code editor. The {i} icon also uses a non-proportional font, but omits the coloring.

Pasting code there from EG always preserves my formatting; only when re-editing the code it is better to copy/paste back to EG, remove the original code segment and re-post the edited code in a new window.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Rate Application to Claims Data

Thank you all who have taken a look at my quandary.  I would like to get back to the direct issue, which is how to zero out the claim lines that shouldn't get paid if there is already a 'precendent' payment.  I'm going to remove my code sample since it really didn't work to begin with.

 

Thank you in advance for taking a look.

 

Brian

Solution
‎10-13-2016 08:52 AM
Respected Advisor
Posts: 3,902

Re: Rate Application to Claims Data

[ Edited ]

 

Below two coding options.

Option 1 requires your source data to be pre-sorted by key, Option 2 doesn't require any sorting but requires more memory.

data have;
  infile datalines truncover;
  input Key Line_No Flag Rate;
datalines;
3217 1 29 102.81
3217 2 29 145.78
3217 3 1 2290
3217 4 23 6942
3217 5 8 2418
3218 1 27 1234
3218 2 28 567
3218 3 29 890
;
run;

/* option 1 */
data want1(drop=_:);

  _set_zero_flg='0';
  do until(last.key);
    set have(keep=key flag);
    by key;
    if Flag in (1,2,8) then _set_zero_flg='1';
  end;

  do until(last.key);
    set have;
    by key;
    if _set_zero_flg='1' and Flag not in (1,2,8) then Rate=0;
    output;
  end;

run;

/* option 2 */
data want2;
  set have;

  if _n_=1 then
    do;
      dcl hash h1 (dataset:'have(keep=key Flag where=(Flag in (1,2,8)))',multidata:'n');
      h1.defineKey('key');
      h1.defineDone();
    end;

  if h1.check()=0 and Flag not in (1,2,8) then Rate=0;
run;

 

Occasional Contributor
Posts: 8

Re: Rate Application to Claims Data

Thank you very much for taking the time to look at this.  I knew there was a straightforward solution, but I couldn't see the forest for the trees.

 

Brian

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 329 views
  • 3 likes
  • 3 in conversation