topic Re: Use a Do Loop to Adjust and Output Data in SAS Programming

Use a Do Loop to Adjust and Output Data

theponcer — Wed, 08 May 2019 22:04:26 GMT

I am trying to figure out how much business I would have if all of my customers received oil changes at 90 day intervals. I have a data set that shows me when they have changed their oil in the past, but am having a hard time figuring out how to extrapolate future oil changes from the first oil change.

Does anyone know how to do this? I am guessing it is something like this:

proc sort data=have;
by Name Car Date_of_Service;
quit;

data have2;
set have;
by Name Car Date_of_Service;
if first.Car=1 then output;
run;

data want;
set have2;
by name car;
do while (date_of_service lt '01Jan2019'd);
	if first.car=1 then output want; 
	date_of_service = date_of_service + 90; 
	output want;
end;
run;

To be clear, I want the code to look at the first observation and output it to "want". Then, I want it to change the date_of_service to 90 days in the future and output that observation. Then, I want it to look at the observation it just output - if that date_of_service is in the same year, then I want it to change the date_of_service to 90 days in the future. If the new date_of_service is in the same year, the new observation should be output, and the loop will continue. If not, the loop will move on to the first record of the next name/car combination.

Re: Use a Do Loop to Adjust and Output Data

PaigeMiller — Wed, 08 May 2019 22:07:18 GMT

Since I don't have your data to test, I will recommend a change (but there may be other problems as well)

data want;
set have2;
by name car;
do while (date_of_service lt '01Jan2019'd);
	if first.car then do;
	    date_of_service = date_of_service + 90; 
	    output want;
    end;
end;
run;

Re: Use a Do Loop to Adjust and Output Data

ballardw — Wed, 08 May 2019 22:15:30 GMT

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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 {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Use a Do Loop to Adjust and Output Data

theponcer — Wed, 08 May 2019 22:15:46 GMT

I tried this too - unfortunately, it only outputs one record, and only after modifiying it. I suppose not outputting the first record is fine because I can just append the first records in a separate step, but I DO need those other records .

For example,

If I start from 1/25, I would need the following records:

1/25

4/25

7/24

10/22

This way only outputs 4/25!

Re: Use a Do Loop to Adjust and Output Data

PaigeMiller — Thu, 09 May 2019 11:02:09 GMT

At this point, you need to provide us with (a portion of) your actual data, using the method linked to by @ballardw

Re: Use a Do Loop to Adjust and Output Data

Tom — Thu, 09 May 2019 11:49:23 GMT

Should be easy enough. Probably need to move the OUTPUT before the increment to the date.

data want;
  set have;
  do until (date_of_service ge '01Jan2019'd);
    output;
    date_of_service = date_of_service + 90; 
  end;
run;

Re: Use a Do Loop to Adjust and Output Data

PaigeMiller — Thu, 09 May 2019 12:32:38 GMT

Good catch!

Re: Use a Do Loop to Adjust and Output Data

theponcer — Thu, 09 May 2019 14:52:22 GMT

This didn't quite work - when I use just the first observation as have, this logic simply outputs the first observation without adjusting the date_of_service.

data want;
set have2;
do until (date_of_service ge '01Jan2019'd);
		output want;
	date_of_service = date_of_service + 90; 

end;

run;

If I move the output statement to where it was before, it again outputs the first observation, but changes the date_of_service to 4/25.

data want;
set have2;
do until (date_of_service ge '01Jan2019'd);
	date_of_service = date_of_service + 90; 
	output want;
end;

run;

When I put an output statement in both places, it only outputs the first observation without adjusting the date_of_service.

data want;
set have2;
do until (date_of_service ge '01Jan2019'd);
	output want;
	date_of_service = date_of_service + 90; 
	output want;
end;

run;

I'm using SAS Enterprise Guide, and submitting the code using the "Run Selection" option. I used the Generic SAS Editor to create the code. These are questions you had asked me to answer in another thread, so I figured I would post them here as well in case it is helpful.

Current version: 9.04.01M5P091317

Operating System: LIN X64

Re: Use a Do Loop to Adjust and Output Data

theponcer — Thu, 09 May 2019 16:05:30 GMT

I had set options obs = 1 earlier, and never changed it back to max. Your code works, thanks a bunch!