Keeping subsequent observations matching on a variable

[deengyn]

Hi there, 

The following is my current dataset. 

Fst_pl represents the first and most expensive plastic surgery procedural code per patient. 

I've used the following code to sort it: 

proc sort data = comp_3; 
out = comp_4; 
by pid pl_date descending bill_amount; 
run; 

My question is as follows: These patients have "COMPLEX" as their most expensive billed code for their first plastic surgery. This is a supplementary code that simply added on top to the actual procedural code to correctly bill the surgeon doing the work. So, what I'd like to identify is the next most expensive code matched on the same day. 

How do I retain all dates matching onto the FIRST recorded pl_date per patient? Or at least, how do I retain 2 lines of code for each patient stating the most and second most expensive procedures on the earliest recorded surgery?

I've had ideas as to using a combination of IF ELSE and FIRST statements, but have not been successful. 

[Jagadishkatam]

proc sort data=

proc sort data = comp_3 out = comp_4; 
by pid pl_date descending bill_amount; 
run; 

data want;
set comp_4; 
by pid pl_date;
retain fst_pl2;
if first.pid then fst_pl2=fst_pl;
run;

[ballardw]

First, your sort code is incorrect, there should not be a semicolon before the OUT= option.

It is best to provide a small example of data values needed to do something as a data step. It is very hard to code against a picture.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Then show us what the result should be for the given example data.

For example, if the desire is to have Sum of the Bill_amount by date then you could use proc summary with By (or Class) Pid PL_date. But we kind of need to know what you expect for output.

Retain has very specific meaning in SAS code as there is a function by that name. So if you do not mean "keep the value of a variable across records in a data step" then you need to carefully define what you mean by "retain".

If you mean to subset the data perhaps something like this may be what you want.

data want;
   set have;
   by pid pl_date;
   retain Firstdate;
   if first.pid then firstdate=pl_date;
   If pl_date = firstdate;
   drop firstdate;
run;

This code Retains the value of the first pl_date value included for each PID. The second IF subsets the data to only have the records that match the first pl_date for each PID.

[deengyn]

Thank you for your correcting comments. 

And yes, I will keep a note of providing a data step (I was unsure because the only manipulation I did to the data was sorting). 

But, I will review the link you've provided and incorporate it into subsequent posts. 

Also, will be cautious in using "retain".

Yes, that code is correct, thank you. 
I'm curious to know what would change if instead of having ONLY the first two records, we had all the records that occured on the same day. (Because I'm a novice SAS coder, I'm curious as to the intricate changes to code)

[ballardw]

---

@deengyn wrote:

Thank you for your correcting comments. 

And yes, I will keep a note of providing a data step (I was unsure because the only manipulation I did to the data was sorting). 

But, I will review the link you've provided and incorporate it into subsequent posts. 

Also, will be cautious in using "retain".

Yes, that code is correct, thank you. 
I'm curious to know what would change if instead of having ONLY the first two records, we had all the records that occured on the same day. (Because I'm a novice SAS coder, I'm curious as to the intricate changes to code)

---

It helps to reference the variables. Replacing PL_DATE with SERV_date in the sort and the data step would select all the records for the first serv_date value.

If you want to SUM the billing amount you might look into Proc Report if want an itemized list by Serv_date with a total for each date.

It really helps to show what you want, and describe rules, for a given set of data. There are so many things that might be done...