DATA Step, Macro, Functions and more

proc sql with max (case when...

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

proc sql with max (case when...

 

 

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)


Accepted Solutions
Solution
‎09-14-2017 11:40 AM
Contributor
Posts: 40

Re: proc sql with max (case when...

Sry 

 

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

View solution in original post


All Replies
PROC Star
Posts: 765

Re: proc sql with max (case when...

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?

Contributor
Posts: 40

Re: proc sql with max (case when...

[ Edited ]

yes that was the idea

, by contract_month, by contract

Solution
‎09-14-2017 11:40 AM
Contributor
Posts: 40

Re: proc sql with max (case when...

Sry 

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 125 views
  • 0 likes
  • 2 in conversation