/* 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
*/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.