topic Re: Using a Loop in a data step to populate values in other fields in SAS Programming

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

gossetm3 — Fri, 18 Oct 2019 13:43:58 GMT

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;

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

gamotte — Fri, 18 Oct 2019 13:50:36 GMT

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;"

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

noling — Fri, 18 Oct 2019 14:34:00 GMT

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.

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

ballardw — Fri, 18 Oct 2019 16:46:13 GMT

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.

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

gossetm3 — Fri, 18 Oct 2019 17:28:49 GMT

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;

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

noling — Fri, 18 Oct 2019 18:13:53 GMT

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;

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

gossetm3 — Fri, 18 Oct 2019 18:36:49 GMT

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.

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

noling — Fri, 18 Oct 2019 18:39:44 GMT

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.

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

gossetm3 — Tue, 22 Oct 2019 14:41:41 GMT

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;

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

noling — Tue, 22 Oct 2019 14:58:15 GMT

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.

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

gossetm3 — Tue, 22 Oct 2019 19:34:02 GMT

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?

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

noling — Tue, 22 Oct 2019 19:51:46 GMT

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.

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

gossetm3 — Wed, 23 Oct 2019 11:49:23 GMT

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.

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

noling — Wed, 23 Oct 2019 12:50:57 GMT

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;

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

gossetm3 — Wed, 23 Oct 2019 14:51:53 GMT

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.

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

noling — Wed, 23 Oct 2019 15:13:50 GMT

You're welcome! I know the VBA to SAS transition isn't 1-1.

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

gossetm3 — Wed, 23 Oct 2019 15:33:03 GMT

When I clear out the values if Open, can I also set another field called OpenDt to the book date and keep it regardless of the other status changes for that Claimant/CovCd combo?

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

noling — Wed, 23 Oct 2019 15:41:11 GMT

Sure you can! You'll want to add your new OpenDt field to the RETAIN statement. I suspect you'll also want to clear out that value for each new ClaimNumber. If you don't clear out OpenDt like you do for CloseDt and reopenDT, it will just persist

You'll want to consider how to handle 2 'Open's in a row like you have in rows 38 and 39. If you only want the first one, then you'll need to use some first-DOT processing.

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

Tom — Wed, 23 Oct 2019 16:15:01 GMT

I don't totally follow your logic and since you haven't posted data in a usable format (xslx doesn't really count) not going to play with it.

Note that when you have multiple variables in the BY statement the effect is nested. So if you use BY A B C. The flag FIRST.B is the true in the first observation for a set of observations that have the same value of B within the larger set of observations that have the current value of A also. Another way to think about it is that when you start a new A group then you also start a new B and C group.

To keep track of the first occurrence of an event within a group you can use a flag that tells you whether the event has occurred yet. If you are setting a variable to missing then setting it to non-missing when the event occurs then you might just be able to use that same variable as the flag. So to save the current date into a new variable named first_date when a particular status is found you could use code like this:

if status='LOOKING FOR THIS' and missing(first_date) then first_date = current_date;

Or perhaps more simply:

if status='LOOKING FOR THIS'  then first_date = coalesce(first_date,current_date);

Watch out for observations that meet your status criteria but have missing values of the variable you want to capture. What do you want to do in that case? Should the new variable be left missing? Should we keep looking for another observation with that status that doesn't have a missing value for the other variable?

To further expand your flexibility google the DOW loop concept in SAS data step code. https://www.google.com/search?q=%40sas.com+dow+loop

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

gossetm3 — Wed, 23 Oct 2019 19:23:29 GMT

So yes, I would need to do some first logic because I want the first open date to carry until the last occurrence of that claimant/coverage combo within that claim. I added that and it appears to be working.

Thank you.