topic Re: merging data across variables in SAS Programming

merging data across variables

Joostvanerp — Thu, 13 Aug 2020 10:14:28 GMT

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;

Re: merging data across variables

Kurt_Bremser — Thu, 13 Aug 2020 10:26:06 GMT

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

Re: merging data across variables

Kurt_Bremser — Thu, 13 Aug 2020 10:34:38 GMT

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

Re: merging data across variables

Joostvanerp — Thu, 13 Aug 2020 11:33:53 GMT

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

Re: merging data across variables

Kurt_Bremser — Thu, 13 Aug 2020 11:45:20 GMT

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;