BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Matthijs
Obsidian | Level 7

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

1 ACCEPTED SOLUTION
5 REPLIES 5
Matthijs
Obsidian | Level 7

Thank you!

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

slacey
Obsidian | Level 7
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.
Loko
Barite | Level 11

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;
slacey
Obsidian | Level 7

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;
	

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1072 views
  • 1 like
  • 4 in conversation