https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395982#M95569 <P>Sry&nbsp;</P> <P>&nbsp;</P> <P>Just need to sort the other way and go with the last 7 obs</P> Thu, 14 Sep 2017 15:40:18 GMT Kiteulf 2017-09-14T15:40:18Z https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395947#M95564 <P>&nbsp;</P> <P>&nbsp;</P> <P>I am trying to get the next 6 observations, by contract_month, so that if bad is one , then my result should be one.</P> <P>&nbsp;</P> <P>like</P> <P>&nbsp;</P> <TABLE width="458"> <TBODY> <TR> <TD width="82">ContractNbr</TD> <TD width="136">underlying</TD> <TD width="30">Bad</TD> <TD width="35">mob</TD> <TD width="111">contract_month</TD> <TD width="64">Result</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>3</TD> <TD>20000861-201402</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>4</TD> <TD>20000861-201403</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>5</TD> <TD>20000861-201404</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>6</TD> <TD>20000861-201405</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>7</TD> <TD>20000861-201406</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>8</TD> <TD>20000861-201407</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>9</TD> <TD>20000861-201408</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>10</TD> <TD>20000861-201409</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>11</TD> <TD>20000861-201410</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>12</TD> <TD>20000861-201411</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>4</TD> <TD>1</TD> <TD>13</TD> <TD>20000861-201412</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>4</TD> <TD>1</TD> <TD>14</TD> <TD>20000861-201501</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>4</TD> <TD>1</TD> <TD>15</TD> <TD>20000861-201502</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>5</TD> <TD>1</TD> <TD>16</TD> <TD>20000861-201503</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>6</TD> <TD>1</TD> <TD>17</TD> <TD>20000861-201504</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>5</TD> <TD>1</TD> <TD>18</TD> <TD>20000861-201505</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>6</TD> <TD>1</TD> <TD>19</TD> <TD>20000861-201506</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>7</TD> <TD>1</TD> <TD>20</TD> <TD>20000861-201507</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>8</TD> <TD>1</TD> <TD>21</TD> <TD>20000861-201508</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>22</TD> <TD>20000861-201509</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>23</TD> <TD>20000861-201510</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>24</TD> <TD>20000861-201511</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>25</TD> <TD>20000861-201512</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>26</TD> <TD>20000861-201601</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>27</TD> <TD>20000861-201602</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>28</TD> <TD>20000861-201603</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>29</TD> <TD>20000861-201604</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>30</TD> <TD>20000861-201605</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>31</TD> <TD>20000861-201606</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>32</TD> <TD>20000861-201607</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>33</TD> <TD>20000861-201608</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>34</TD> <TD>20000861-201609</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>35</TD> <TD>20000861-201610</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>36</TD> <TD>20000861-201611</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>37</TD> <TD>20000861-201612</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>1</TD> <TD>0</TD> <TD>38</TD> <TD>20000861-201701</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>2</TD> <TD>0</TD> <TD>39</TD> <TD>20000861-201702</TD> <TD>1</TD> </TR> <TR> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>40</TD> <TD>20000861-201703</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>41</TD> <TD>20000861-201704</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>42</TD> <TD>20000861-201705</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>43</TD> <TD>20000861-201706</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>44</TD> <TD>20000861-201707</TD> <TD>0</TD> </TR> <TR> <TD>1</TD> <TD>0</TD> <TD>0</TD> <TD>45</TD> <TD>20000861-201708</TD> <TD> <P>0</P> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>In excel the formula is like</P> <P>=IF(SUM(C3:C8)&gt;=1;1;0)</P> Thu, 14 Sep 2017 14:59:53 GMT https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395947#M95564 Kiteulf 2017-09-14T14:59:53Z https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395954#M95566 <P>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?</P> Thu, 14 Sep 2017 15:08:27 GMT https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395954#M95566 PeterClemmensen 2017-09-14T15:08:27Z https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395957#M95567 <P>yes that was the idea</P> <P>, by contract_month, by contract</P> Thu, 14 Sep 2017 15:12:08 GMT https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395957#M95567 Kiteulf 2017-09-14T15:12:08Z https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395982#M95569 <P>Sry&nbsp;</P> <P>&nbsp;</P> <P>Just need to sort the other way and go with the last 7 obs</P> Thu, 14 Sep 2017 15:40:18 GMT https://communities.sas.com/t5/SAS-Programming/proc-sql-with-max-case-when/m-p/395982#M95569 Kiteulf 2017-09-14T15:40:18Z