Using a Loop in a data step to populate values in other fields

[gossetm3]

I am trying to use a data step to populate two fields, based on the values of two other fields and I am running into issues.  I have attached a sample of what I want my output to look like.  I have highlighted the values that I want to populate and what I want those values to equal.

Basically, I have a data set that is sorted by a claim number and a sequence number, so that the data is in chronological (transaction) order.  For each claim number, when the status is 'Open', I want the Closed Date and the Reopen Date fields to be null.  When the status changes from Open to Closed, I want to take the Book Date, which is a transaction date, and put it into the Closed Date field.  Then, for each record after that where the status is = 'Closed', I want to use that first Book Date in the Closed Date field, until the status changes again.  When the status changes to Reopen, I want to make the Close Date = null and the reopen date to equal the Book Date.  I will then use that first Book Date value in each following record until the status changes again.  Each time the status changes, I need to basically reset the date I am using to the value in the current record of the first change.

I am using SAS EG 7.15 HF7 (7.100.5.6177) (64-bit)

I started with this Do While Loop, but the last END; keeps flagging an error "No Matching DO/SELECT statement".

DATA WORK.STATUS_LIST_1;

        SET WORK.STATUS_LIST;

        clmnum = ClaimNumber;

        DO WHILE (clmnum = ClaimNumber);

               If ClaimStatusCd NE 'Closed' THEN
                       OUTPUT;
               ELSE
                       keepClosedDt = BookDt;
                       keepStatusCd = ClaimStatusCd;
                       ClosedDt = keepClosedDt;
                       OUTPUT;
               END;

               DO WHILE (keepStatusCd = ClaimStatusCd);
                       ClosedDt = keepClosedDt;
                       OUTPUT;
               END;
           
        END;

run;

[gamotte]

Hello,

If you want to execute several instructions after an if or an else you have to enclose

them in do; ... end;

Here your else should be "else do;"

[noling]

This is going to give you an infinite loop since you're setting clmnum = ClaimNumber, using those same values in the WHILE loop, and not changing their values in the loop.

I'm assuming that you probably want to look at the ClaimNumber value of a different record. You could use the LAG function, a RETAIN statement, or first-dot last-dot by group processing.

[ballardw]

---

DATA WORK.STATUS_LIST_1;
        SET WORK.STATUS_LIST;
        clmnum = ClaimNumber;
        DO WHILE (clmnum = ClaimNumber);
               If ClaimStatusCd NE 'Closed' THEN
                      OUTPUT;
               ELSE DO;
                       keepClosedDt = BookDt;
                       keepStatusCd = ClaimStatusCd;
                       ClosedDt = keepClosedDt;
                       OUTPUT;
               END;
               DO WHILE (keepStatusCd = ClaimStatusCd);
                       ClosedDt = keepClosedDt;
                       OUTPUT;
               END;
        END;
run;

---

Cause of error missing DO.

A do while needs the value(s) used in the comparison to change or they won't end. So I suspect that you are going to have an infinite loop.

BEST, provide a small example data set of what you have, only include variables needed, and an example of how the result should look.

"Status Change" means that you need to compare a current record value to a previous record value, which would imply use of either the LAG function, to look back, or the RETAIN, to hold a previous value.

[gossetm3]

Thank you everyone.  The DO did correct the error I was getting but the logic I created, did in fact, create an infinite loop.  I reviewed the LAG and retain logic online and wrote the below.  This is close to what I need, but I need to finish it out.  The below code works for the first record of each grouping of claimnumber and status code.  I would like to take CloseDt value and pass it on to each following record until the status changes again.  Once a claim is in Open status, the CloseDt would be null.  I am trying to figure out how to apply the retain and or lag to this.  I am thinking that if it is the first record combo, then use the below code, if it is the second or greater combo, retain the first result until the next combo.

DATA WORK.STATUS_LIST_1; 


	SET WORK.STATUS_LIST;

	format CloseDT DATE9.;

	BY ClaimNumber;

	IF ClaimStatusCd = 'Closed' THEN
		CloseDT = BookDt;
		OUTPUT;

RUN;

[noling · Accepted solution]

Some points:

1. If you want several statements to happen as a result of an IF condition, you need to put them in a DO/END block:

IF claimNumber_lag = claimNumber and ClaimStatusCd = 'Closed' then do;
	CloseDT = BookDt;
	output;
end;

2. You don't need to use an OUTPUT statement without any conditions - SAS will output automatically at the bottom of each datastep if you don't use any OUTPUT statements.

3. Using a BY statement without referencing the variables in the statement doesn't do anything. In the example below, first.claimnumber = 1 when the value of claimNumber is different from the previous value of claimNumber (otherwise = 0)

4. If you want to use a LAG function, create it outside of conditional logic.

--- 

I think this may be a cleaner solution for you. The RETAIN statements holds values across records, so in this example you clear them out for each new claim, then adjust them according to your desired specifications. Hopefully this is close:

data WORK.STATUS_LIST_1; 
	set WORK.sample;
	format  closeDt reopenDt date9.;

	/*carry over values of closeDt and reopenDate over records*/
	retain closeDt reopenDt;

	by ClaimNumber claimStatusCd notsorted;

	/*clear retained values for each new claim*/
	/*call missing() sets variables to missing/null*/
	if first.claimNumber then call missing(closeDt, reopenDt);

	/*first.status = 1 when claimStatusCd has changed since the previous record*/
	if first.claimStatusCd and claimStatusCd = 'Open' then call missing(closeDt, reopenDt);
	if first.claimStatusCd and claimStatusCd = 'Closed' then do;
		closeDt = bookDt;
		call missing(reopenDt);
	end;
	if first.claimStatusCd and claimStatusCd = 'Reopen' then do; 
		call missing(closeDt);
		reopenDt = bookDt;
	end;

	/*SAS outputs automatically if you do not have OUTPUT statements*/
run;

[gossetm3]

Thank you very much.  That is the exact result I was looking for.  I know how to do this in VBA but have never attempted this in SAS before.  I really appreciate the assistance and now I have a model that I can use going forward with similar data sets that I will have to complete.

[noling]

You're welcome! I struggled with the VBA-to-SAS transition not being able to just go back up a row and easily reference previous values.

[gossetm3]

So, I am doing the exact same thing, but I now need to use 4 columns instead of two.  I took the code above and tried to modify it but I the results were not correct.  Below was how I edited the code, so it appears that I do not understand the code as much as I thought I did.  I have attached another sample with the results I am looking for.  Any additional assistance would be appreciated

/*This Data Step will assign the first Book Date to each change in status, at the exposure level, until a new status change is detected on the exposure.*/
DATA WORK.STATUS_LIST_EXPOSURE_1; 
	set WORK.STATUS_LIST_EXPOSURE;

	format  closeDt reopenDt date9.;

	/*carry over values of closeDt and reopenDate over records*/
	retain closeDt reopenDt;

	by ClaimNumber ClaimantCd CoverageCd FeatureStatusCd notsorted;

	/*clear retained values for each new claim*/
	/*call missing() sets variables to missing/null*/
	if first.claimNumber then call missing(closeDt, reopenDt);

	/*first.status = 1 when FeatureStatusCd has changed since the previous record*/
	if first.claimNumber AND first.ClaimantCd AND first.CoverageCd AND FeatureStatusCd = 'Open' then call missing(closeDt, reopenDt);
	if first.claimNumber AND first.ClaimantCd AND first.CoverageCd AND FeatureStatusCd = 'Closed' then do;
		closeDt = bookDt;
		call missing(reopenDt);
	end;
	if first.claimNumber AND first.ClaimantCd AND first.CoverageCd AND FeatureStatusCd = 'Reopen' then do; 
		call missing(closeDt);
		reopenDt = bookDt;
	end;

	/*SAS outputs automatically if you do not have OUTPUT statements*/
run;

[noling]

So there's 2 concepts here:

1. RETAIN statement - this holds values across rows. This is how you can have the same value of closeDt many times without having to set it each row.

2. First-DOT processing. We're assuming that your records in your BY statement are in the correct order (the notsorted option at the end allows records to not be sorted, if desired). The value of first.<variable> will be 1 when the field is different from the previous value of that field (if it's in the BY statement). Otherwise 0. The same is true of last.<variable>, except this is true when the field is the last occurrence of the current value.

This is easier to see - note how the fields are 1 or 0 depending on their first/last values by column order:

data example;
	set STATUS_LIST_EXPOSURE;

	by ClaimNumber ClaimantCd CoverageCd FeatureStatusCd notsorted;

	first_claimNumber=first.ClaimNumber;
	last_ClaimNumber=last.ClaimNumber;

	first_ClaimantCd=first.ClaimantCd;
	last_ClaimantCd=last.ClaimantCd;

	first_CoverageCd=first.CoverageCd;
	last_CoverageCd=last.CoverageCd;
run; 

Check your first-DOT logic against the values using this new dataset as a visual.

Hint: check your first.CoverageCd.

[gossetm3]

Thank you.  I can see the data now and it does make sense now.  So, looking at the ones and zeros in the data, the records that I want to see are all 0's, so my results come back blank.

So, I guess the best way to put this is that I want the first combination of all of those fields where the one field is Open, etc....  Should I concatenate those three fields and use the first code you sent me on the concatenated field, in place of the claimnumber?

[noling]

You probably don't need to concatenate anything. Has your business logic changed? I now see that your StatSequence and BookDt is out of order. What are you trying to accomplish with each column? Your new sample data makes much less sense to me.

[gossetm3]

In the first data set, I am taking the claim, the status and finding the first time the status changes and applying that book date to all transactions after that until the status changes again.  In this new data set, I am doing the exact same thing, except that I am breaking out a claim into its different parts.

In a claim, we have parts of the claim that can open and close while the claim, as a whole, remains open.  In my sample data, each part is defined as the combination of claimant number and coverage code.  

For each claimant/coverage combination within a claim, I need to populate those closed and reopen dates using the same logic as my first data set.

You can ignore the statsequ number.  That field simply numbers all transaction across all claims, in order of when the transactions occurred.  It is primarily used to order transactions that occur on the same date.

[noling]

I think this is what you need. This doesn't use the CoverageCd in the BY statement. I see that you carry over the 20-Feb-14 value from the last AA coverageCd to the first BB coverageCd. This clears values when you 1.) start a new claimnumber, 2.) start a new claimantCd, or 3.) Reach an 'Open'

This matches your excel sheet, but I'm not 100% if if matches your business logic.

data want;
	set have;
	format closeDt reopenDt date9.;

	/*carry over values of closeDt and reopenDate over records*/
	retain closeDt reopenDt;

	/*I removed coverageCd here since it looks like you carry over the value of 
	20-Feb-14 from line 8 to 9 (AA to BB)*/
	by ClaimNumber ClaimantCd FeatureStatusCd notsorted;

	/*looks like you clear values for new claimantCd, and not CoverageCds*/
	if first.claimNumber or first.claimantCd then call missing(closeDt, reopenDt);

	/*clear values if 'Open'*/
	if FeatureStatusCd = 'Open' then call missing(closeDt, reopenDt);

	/*if a new 'Closed'*/
	if FeatureStatusCd = 'Closed' and first.FeatureStatusCd then do;
		closeDt = bookDt;
		call missing(reopenDt);
	end;
	/*if a new 'reopen'*/
	if FeatureStatusCd = 'Reopen' and first.FeatureStatusCd then do; 
		call missing(closeDt);
		reopenDt = bookDt;
	end;

	/*SAS outputs automatically if you do not have OUTPUT statements*/
run;

[gossetm3]

Actually, that AA to BB carry of the date was an error.  I need it to reset with each Claim, Claimant and Coverage combo.  I reattached the excel file with the correction.

I added the coveragecd back into the by statement and it appears to work as expected.

Once again, thank you.