merging data across variables

[Joostvanerp]

Hi all,

I would like to transfom my dataset for further analysis.
The goal is to get the data together with the same selectionnumber, to summate the intervals with the same selection numbers, except for the last interval with that selectionnumber. In stead of the interval, the seconds of that line needs to be used in the place of the interval in the summation.
I can't get it working.

BR, Joost

/* input dataset */
data test(drop=datetm rename=(datetm1=datetm));
length cownum 8 datetm $ 16 interval 8 selectionnm 8 seconds;
infile datalines dsd dlm="09"x;
input cownum  datetm $ interval  selectionnm seconds;
/* As datetime is in character,it needs to be traslated into datetime */
datetm1=input(datetm,datetime.);
format datetm1 nldatm48.;
cards;
11	13MAR20:09:10:41	14	1 10
11	13MAR20:09:10:55	46	1 20
11	13MAR20:09:11:41	60	1 35
11	13MAR20:09:12:41	35	2 28
14	13MAR20:09:10:41	120	1 50
14	13MAR20:09:12:41	60	2 10
14	13MAR20:09:13:41	60	3 40
14	13MAR20:09:14:41	35	3 30
;
run;

[Kurt_Bremser]

So you want to keep all observations, but with a running summation of intervals, except for the last observation in a group?

[Kurt_Bremser]

You should supply an expected outcome for the data you posted.

[Joostvanerp]

11	13MAR20:09:10:41	95	1 35
11	13MAR20:09:12:41	28	2 28
14	13MAR20:09:10:41	50	1 50
14	13MAR20:09:12:41	10	2 10
14	13MAR20:09:13:41	90	3 30

Expected outcome 

[Kurt_Bremser · Accepted solution]

Then this code does it:

data have;
input cownum datetm :datetime. interval selectionnm seconds;
format datetm datetime19.;
cards;
11  13MAR20:09:10:41  14  1 10
11  13MAR20:09:10:55  46  1 20
11  13MAR20:09:11:41  60  1 35
11  13MAR20:09:12:41  35  2 28
14  13MAR20:09:10:41  120 1 50
14  13MAR20:09:12:41  60  2 10
14  13MAR20:09:13:41  60  3 40
14  13MAR20:09:14:41  35  3 30
;

data want;
retain cownum datetm interval selectionnm seconds;
format datetm datetime19.;
retain datetm;
set have (rename=(datetm=_datetm interval=_int));
by cownum selectionnm;
if first.selectionnm
then do;
  datetm = _datetm;
  interval = ifn(last.selectionnm,seconds,_int);
end;
else interval + ifn(last.selectionnm,seconds,_int);
if last.selectionnm;
drop _:;
run;