proc sql with max (case when...

[Kiteulf]

I am trying to get the next 6 observations, by contract_month, so that if bad is one , then my result should be one.

like

ContractNbr	underlying	Bad	mob	contract_month	Result
1	1	0	3	20000861-201402	0
1	1	0	4	20000861-201403	1
1	1	0	5	20000861-201404	1
1	1	0	6	20000861-201405	1
1	1	0	7	20000861-201406	1
1	2	0	8	20000861-201407	1
1	2	0	9	20000861-201408	1
1	3	1	10	20000861-201409	1
1	3	1	11	20000861-201410	1
1	3	1	12	20000861-201411	1
1	4	1	13	20000861-201412	1
1	4	1	14	20000861-201501	1
1	4	1	15	20000861-201502	1
1	5	1	16	20000861-201503	1
1	6	1	17	20000861-201504	1
1	5	1	18	20000861-201505	1
1	6	1	19	20000861-201506	1
1	7	1	20	20000861-201507	1
1	8	1	21	20000861-201508	1
1	1	0	22	20000861-201509	1
1	2	0	23	20000861-201510	1
1	3	1	24	20000861-201511	1
1	3	1	25	20000861-201512	0
1	2	0	26	20000861-201601	0
1	0	0	27	20000861-201602	0
1	0	0	28	20000861-201603	0
1	0	0	29	20000861-201604	0
1	1	0	30	20000861-201605	1
1	1	0	31	20000861-201606	1
1	1	0	32	20000861-201607	1
1	1	0	33	20000861-201608	1
1	2	0	34	20000861-201609	1
1	2	0	35	20000861-201610	1
1	3	1	36	20000861-201611	1
1	0	0	37	20000861-201612	1
1	1	0	38	20000861-201701	1
1	2	0	39	20000861-201702	1
1	3	1	40	20000861-201703	0
1	0	0	41	20000861-201704	0
1	0	0	42	20000861-201705	0
1	0	0	43	20000861-201706	0
1	0	0	44	20000861-201707	0
1	0	0	45	20000861-201708	0

In excel the formula is like

=IF(SUM(C3:C8)>=1;1;0)

[PeterClemmensen]

So you want to look 6 observations ahead, and if the sum of these six values of bad is greater than zero, Result is set to 1, am I correct?

[Kiteulf]

yes that was the idea

, by contract_month, by contract

[Kiteulf · Accepted solution]

Sry 

Just need to sort the other way and go with the last 7 obs