How to loop through claim dates by member

[jpage]

I need to examine claims for each member in the table and pull out the earliest claim date where that has a specific criteria in a list. I have seen posts about using BY but I can not get this to loop through each claim by member properly. Can anyone provide general code for looping through each claim (claimid) by each member (memberid) and pulling out the date?

[mkeintz]

How is your claims data currently organized?  In particular is there already some sort of chronological sequence in the data?

[jpage]

not currently ordered. I know there is a sort function but I did not think it would be necessary. 

For more details let's say a member has 5 claims during the year and of the 5, 3 fit my criteria that I am just using a simple IF statement for. I want to pull the date of the earliest of the 3 claims that fit the criteria by member. It is the by member part I am having the issue with.

I appreciate your help.

[ballardw]

---

@jpage wrote:

not currently ordered. I know there is a sort function but I did not think it would be necessary. 

For more details let's say a member has 5 claims during the year and of the 5, 3 fit my criteria that I am just using a simple IF statement for. I want to pull the date of the earliest of the 3 claims that fit the criteria by member. It is the by member part I am having the issue with.

I appreciate your help.

---

Data.

Data is helpful.

Here is an example using a data set that you should have available to run the code against:

/*sashelp.class substitues for your data set
  If weight > 60 selects only the records that match
  criteria of having a weight greater than 60,
  use your criteria
*/
data example;
  set sashelp.class;
  if weight > 60;
run;
/* think of age as the member identification
   and height as the date (or other variable that you
   want the lowest (first for dates) value
*/
proc sort data=example;
   by age height;
run;
/* subset to only the lowest height 
   per age group among records
   that meet criteria
*/
data want;
   set example
   by age;
   if first.age;
run;

Likely problems:

Your date may be character or in some form other than a SAS date value that will not sort or compare properly for determining "first".

If you have multiple criteria and a member has records that meet multiple criteria your description may be incomplete and you want "first per met criteria" not "first meeting any criteria" which the above example will do. This can be done BUT we need actual concrete examples.

I

[ballardw]

Why  do you have to "loop" through anything.

Sort the data by what ever identification (member id and account number or such) and date is available.

Select records with your "criteria" using an IF statement.

Generic question gets generic advise.

If you provide example data in the form of data step code, or possibly even text (paste text into a text box opened on this forum with the </> icon above the message window to prevent the forum software from reformatting simple text) and the specific criteria you may get code.

[Reeza]

Data step loops automatically so you don't.
Sort and then use FIRST.

proc sort data=have out=filtered;
where filteringCriteria goes here;
by member date;
run;

data firstClaim;
set filtered;
by member;
if first.member; *will be first in data after the sort;
run;

[mkeintz]

As others have said, the SAS data steps and PROCs examine all the observations - i.e. they can "loop through" claim dates by member.  You could code a couple steps to sort/filter and extract the minimum date for each member, while keeping all the other variables to identify the observation of interest.

But this can also be done by the highly useful PROC SUMMARY (alias PROC MEANS) procedure.  Using a sort of SAS pseudo-code applied to your dataset (call it dataset HAVE), it would be something like:

proc summary data=have nway;
  where  criterion_variable in (comma-separated-list-of-values);
  class member ;
  var claims_date;
  output out=want (drop=_type_ _freq_) 
    min=min_claims_date  
    idgroup (last out (othervar1 othervar2 othervar3)=) ;
run;

The WHERE statement applies a filter to examine only each case that "has a specific criteria in a list".

The CLASS statement tells PROC SUMMARY to apply its logic to each member (and the "nway" parameter tells the proc not to also analyze the entire dataset as a single group).

The OUTPUT statement describes what you want your output dataset (named WANT) to contain.

PROC SUMMARY can be a deep study, but is worth it.