/* 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
*/
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.