/* test data */
data one;
input id date :anydtdte. code amt1 amt2;
cards;
1 6/16/2004 100 90 63
1 6/16/2004 200 350 245
1 6/23/2004 200 350 245
1 6/23/2004 100 90 63
1 6/26/2004 100 90 63
1 6/28/2004 100 90 63
1 6/28/2004 200 350 245
1 6/30/2004 200 350 245
1 6/30/2004 100 90 63
1 7/1/2004 200 350 245
1 7/1/2004 100 95 66.5
1 7/3/2004 300 1275 892.5
1 7/3/2004 300 130 91
1 7/6/2004 500 625 437.5
1 7/6/2004 300 210 94.7
1 7/21/2004 300 60 31.83
1 7/28/2004 300 60 31.83
1 8/10/2004 300 85 30
1 8/10/2004 300 85 55.26
1 8/10/2004 300 350 138.04
2 7/25/2001 300 20 14.25
2 7/25/2001 200 350 175
2 7/25/2001 200 350 15
2 7/31/2001 300 20 0
2 7/31/2001 300 20 14.25
2 8/2/2001 300 20 0
2 8/2/2001 300 20 14.25
2 8/2/2001 100 90 59.1
2 8/2/2001 100 90 0.75
2 8/2/2001 200 350 190
2 8/4/2001 300 20 0
2 8/4/2001 300 20 14.25
2 8/4/2001 200 350 189.25
2 8/4/2001 200 350 0.75
2 8/6/2001 300 20 0
2 8/6/2001 300 20 14.25
2 8/6/2001 200 350 175
2 8/6/2001 200 350 15
2 8/8/2001 300 1275 1020
2 8/8/2001 300 130 125.4
2 8/11/2001 500 600 480
2 12/4/2001 300 20 14.25
2 12/4/2001 100 90 44.85
2 12/4/2001 100 90 15
2 12/8/2001 300 20 0
2 12/8/2001 300 20 14.25
2 12/8/2001 200 350 175
2 12/8/2001 200 350 15
2 12/7/2001 200 350 175
2 12/7/2001 200 350 15
2 12/7/2001 300 20 14.25
2 12/10/2001 300 20 0
2 12/10/2001 300 20 14.25
2 12/10/2001 200 350 189.25
2 12/10/2001 200 350 0.75
2 12/12/2001 300 1275 892.5
2 12/12/2001 300 130 125.4
2 12/15/2001 500 600 420
;
run;
proc sort data=one;
by id date;
run;
data two;
length id start finish tot1 tot2 8;
format start finish mmddyy10.;
keep id--tot2;
do until (last.id);
link setOne;
if code = 100 then do;
link init;
do while (1);
link doSum;
if code = 500 | last.id then do;
link doOut;
leave;
end;
link setOne;
end;
end;
end;
return;
setOne:
set one;
by id date;
return;
init:
call missing(tot1, tot2);
start = date;
finish = .;
return;
doSum:
tot1 + amt1;
tot2 + amt2;
return;
doOut:
finish = date;
output;
return;
run;
/* check */
proc print data=two noobs;
run;
/* on lst
id start finish tot1 tot2
1 06/16/2004 07/06/2004 4325 3027.50
2 08/02/2001 08/11/2001 4015 2283.75
2 12/04/2001 12/15/2001 4385 2110.50
*/
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.
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.