Grouping Trends by Monotonicity

[skipjimroo]

Hi there,

I I have output from proc UCM with the additive outliers (spikes) and level shifts labelled. (I am running in9.4.) I want to: 

• Extend these labels to the start and end of the increase period. The start period is the month before the increase. The end period is the last month of the increase.
• Then summarise the data so that only the start and end dates and values are shown.
• I also want level shifts to take precedence, so that if a level shift and spike both occur during this increase period, then the increase is classed as a level shift.

The input data looks like this

data got;
    infile datalines dsd delimiter='|';
format ID $1. Date date9. value 8. valuediff 8. breaktype $12.;
informat date ddmmyy10.; 
   input ID Date Value ValueDiff Breaktype;
    datalines;
A|01/01/2022|1602040||NoBreak
A|01/02/2022|1997341|395301|NoBreak
A|01/03/2022|45544|-1951797|NoBreak
A|01/04/2022|138421|92877|LevelShift
A|01/05/2022|999286|860865|NoBreak
A|01/06/2022|1178399|179113|Spike
A|01/07/2022|881297|-297102|NoBreak
A|01/08/2022|949765|68468|LevelShift
A|01/09/2022|1308423|358658|NoBreak
A|01/10/2022|1004914|-303509|NoBreak
A|01/11/2022|1191019|186105|NoBreak
A|01/12/2022|803331|-387688|NoBreak
B|01/01/2022|921410||NoBreak
B|01/02/2022|1068787|147377|NoBreak
B|01/03/2022|297284|771503|NoBreak
B|01/04/2022|338337|41053|LevelShift
B|01/05/2022|487583|149246|NoBreak
B|01/06/2022|696031|208448|Spike
B|01/07/2022|689942|6089|NoBreak
B|01/08/2022|763417|73475|LevelShift
B|01/09/2022|983677|220260|NoBreak
B|01/10/2022|545737|437940|NoBreak
B|01/11/2022|740081|194344|NoBreak
B|01/12/2022|533809|206272|NoBreak
;
run;

My target data should look like this:

ID	break_startdate	break_enddate	breaktype	startbreak_value	endbreak_value
A	01/03/2022	01/06/2022	LevelShift	45544	1178399
A	01/07/2022	01/09/2022	LevelShift	881297	1308423
B	01/03/2022	01/06/2022	LevelShift	297284	696031
B	01/07/2022	01/09/2022	LevelShift	689942	983677

I have created some code that works in some circumstances, but not all.

• It cant handle multiple breaks in the same id.
• It doesnt let level shifts take precedence over spikes.
• And it doesnt work if the break label isnt at the start of the increase. I have tried using lag functions, but the issue is that you dont know how many rows to lag by.

data need
(keep=id

startbreakvalue endbreakvalue newbreak
structbreak_startdate structbreak_enddate rename=(newbreak=breaktype));
set got;
by id date breaktype;
retain structbreak_startdate structbreak_enddate
startbreakvalue endbreakvalue newbreak;
format structbreak_startdate structbreak_enddate date9.;

if first.id =1   then do;
startbreakvalue = 99999;
endbreakvalue = 0;
structbreak_startdate = '31dec3500'd;
structbreak_enddate = '1jan1960'd;
newbreak =breaktype;
end;
else do;
if breaktype ne "NoBreak" then do;
newbreak =breaktype;
structbreak_enddate ='31dec3500'd;
structbreak_startdate =intnx('month',date,-1);
startbreakvalue = value-valuediff;
end;

if breaktype = "NoBreak" and valuediff<0 then do;
x=1;
structbreak_enddate =min(structbreak_enddate,intnx('month',date,-1));
endbreakvalue = max(endbreakvalue,value-valuediff);
end;
end;
if last.id = 1;

;
run;

Any help would be greatly appreciated!