topic Calculated 'end' and use as 'start' for next observation in SAS Programming

Calculated 'end' and use as 'start' for next observation

Matthijs — Fri, 24 Jun 2016 13:11:37 GMT

Hi,

I could really use some help. I have the following Excel-data

Input

date	id	vendorid	start	in	out	end
01-01-2017	1000001	001	177893	4800	3100
01-02-2017	1000001	001		4400	2800
01-03-2017	1000001	001		4100	2800
01-04-2017	1000001	001		4300	2700
01-05-2017	1000001	001		3500	2500
01-01-2017	1000001	002
01-02-2017	1000001	002
01-03-2017	1000001	002
01-04-2017	1000001	002
01-05-2017	1000001	002
01-01-2017	1000002	001	5000	550	500
01-02-2017	1000002	001		152	250
01-03-2017	1000002	001		354	354
01-04-2017	1000002	001		1234	986
01-05-2017	1000002	001		854	785

I want to:

1. Calculate 'end' --> = start + (in - out)

2. use the 'end' as 'start' in the next observation (within the same id & vendorid)

Output

date	id	vendorid	start	in	out	end
01-01-2017	1000001	001	177893	4800	3100	179593
01-02-2017	1000001	001	179593	4400	2800	181193
01-03-2017	1000001	001	181193	4100	2800	182493
01-04-2017	1000001	001	182493	4300	2700	184093
01-05-2017	1000001	001	184093	3500	2500	185093
01-01-2017	1000001	002
01-02-2017	1000001	002
01-03-2017	1000001	002
01-04-2017	1000001	002
01-05-2017	1000001	002
01-01-2017	1000002	001	5000	550	500	5050
01-02-2017	1000002	001	5050	152	250	4952
01-03-2017	1000002	001	4952	354	354	4952
01-04-2017	1000002	001	4952	1234	986	5200
01-05-2017	1000002	001	5200	854	785	5269

What is the best way to do this in SAS? Normally i would provide my code, but my results so far aren't even close.

Thank you for your time and advise.

Matthijs

Re: Calculated 'end' and use as 'start' for next observation

Kurt_Bremser — Fri, 24 Jun 2016 13:19:09 GMT

data want (drop=prev_end);
set have;
by id;
retain prev_end;
if not first.id
then start = prev_end;
end = start + in - out;
prev_end = end;
run;

Re: Calculated 'end' and use as 'start' for next observation

Loko — Fri, 24 Jun 2016 13:24:14 GMT

Hello,

Assuming your data is correctly sorted:

data want;
set have;
by id vendorid;

if first.vendorid then end_keep=start;
end_keep+in-out;

run;

Re: Calculated 'end' and use as 'start' for next observation

Matthijs — Fri, 24 Jun 2016 13:30:52 GMT

Thank you!

Now I know what i did wrong in my attempts. I made it way too complex.

Re: Calculated 'end' and use as 'start' for next observation

slacey — Fri, 24 Jun 2016 13:33:32 GMT

Here's a solution that works for the situation. One thing to note about it: when reading in the date variable I converted it to numeric to make sure it would always be sorted chronologically in the sort statement (otherwise we would have, for example, 02-03-2016 being considered earlier than 11-03-2010).

data ds1;
	infile datalines missover;
	input date id vendorid in out start;
	informat date mmddyy10.;
	format date date9.;

datalines;
01-01-2017	1000001	001 4800 3100 177893	 
01-02-2017	1000001	001 4400 2800	 
01-03-2017	1000001	001 4100 2800	 
01-04-2017	1000001	001 4300 2700	 
01-05-2017	1000001	001 3500 2500	 
01-01-2017	1000001	002	 	 	 	 
01-02-2017	1000001	002	 	 	 	 
01-03-2017	1000001	002	 	 	 	 
01-04-2017	1000001	002	 	 	 	 
01-05-2017	1000001	002	 	 	 	 
01-01-2017	1000002	001 550 500 5000 
01-02-2017	1000002	001 152 250	 
01-03-2017	1000002	001 354	354	 
01-04-2017	1000002	001 1234 986	 
01-05-2017	1000002	001 854	785	 
;
run;

proc sort data=ds1; by id vendorid date; run;

data ds2(drop=currend);
	retain currend;
	set ds1;
	by id vendorid;

	if first.vendorid then
		do;
			currend=.;
		end;
	else
		do;
			start=currend;
		end;

	if ^missing(in) and ^missing(out) then 
		do;
			currend=start+(in-out);
			end=currend;
		end;

	output;
run;

Re: Calculated 'end' and use as 'start' for next observation

slacey — Fri, 24 Jun 2016 13:50:36 GMT

I was too slow on the draw.

The only thing I would add to the solution is to make sure your data is properly sorted. Specifically be careful with date sorting. Based on the current format 02-03-2016 would be sorted as earlier than 11-03-2010.