About gossetm3

gossetm3 · ‎10-23-2019

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.

gossetm3 · ‎10-23-2019

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?

gossetm3 · ‎10-23-2019

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.

gossetm3 · ‎10-23-2019

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.

gossetm3 · ‎10-22-2019

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?

gossetm3 · ‎10-22-2019

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;

gossetm3 · ‎10-18-2019

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.

gossetm3 · ‎10-18-2019

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;

gossetm3 · ‎10-18-2019

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;

Online Status	Offline
Date Last Visited	‎10-23-2019 02:12 PM

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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