topic WANT TO FIND OUT M1 Balances in SAS Programming https://communities.sas.com/t5/SAS-Programming/WANT-TO-FIND-OUT-M1-Balances/m-p/346947#M80051 <P>Hi,</P> <P>&nbsp;</P> <P>i have sas data set with the field Account number , ac_opn_date, and month on month balances . i want to create new dataset with Month one (m1) balances against the account number on the basis of account open date .</P> <P>&nbsp;</P> <P>for eg , for account number 1 account open date is Mar 16 then month balance is Apr_16 and for acccount nmer 2 account open date is Apr 2016 then month one balance is May_16, data is huge and i dont want to use if then else contition&nbsp;</P> <P>&nbsp;</P> <TABLE width="670"> <TBODY> <TR> <TD width="80">Account_no</TD> <TD width="88">Ac_opn_date</TD> <TD width="52">Mar_16</TD> <TD width="50">Apr_16</TD> <TD width="54">May_16</TD> <TD width="48">jun_16</TD> <TD width="44">jul_16</TD> <TD width="50">aug_16</TD> <TD width="51">Sep_16</TD> <TD width="49">Oct_16</TD> <TD width="53">Nov_16</TD> <TD width="51">Dec_16</TD> </TR> <TR> <TD>1</TD> <TD>01-Mar-16</TD> <TD>1000</TD> <TD>900</TD> <TD>2000</TD> <TD>1500</TD> <TD>3000</TD> <TD>6000</TD> <TD>6000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> </TR> <TR> <TD>2</TD> <TD>10-Apr-16</TD> <TD>0</TD> <TD>2000</TD> <TD>5000</TD> <TD>4000</TD> <TD>5000</TD> <TD>4000</TD> <TD>4000</TD> <TD>3000</TD> <TD>3000</TD> <TD>3000</TD> </TR> <TR> <TD>3</TD> <TD>15-May-16</TD> <TD>0</TD> <TD>0</TD> <TD>6000</TD> <TD>70000</TD> <TD>8000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> </TR> <TR> <TD>4</TD> <TD>16-Jun-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>8000</TD> <TD>9000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> </TR> <TR> <TD>5</TD> <TD>17-Jul-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>10000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> </TR> <TR> <TD>6</TD> <TD>18-Aug-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>3000</TD> <TD>3000</TD> <TD>30050</TD> <TD>30050</TD> <TD>30000</TD> </TR> <TR> <TD>7</TD> <TD>20-Sep-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>6000</TD> <TD>6055</TD> <TD>6055</TD> <TD>6000</TD> </TR> <TR> <TD>8</TD> <TD>16-Oct-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>10000</TD> <TD>10000</TD> <TD>9000</TD> </TR> <TR> <TD>9</TD> <TD>25-Nov-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>9500</TD> <TD>9000</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>required data set is :</P> <P>&nbsp;</P> <TABLE width="168"> <TBODY> <TR> <TD width="80">Account_no</TD> <TD width="88">M1_AVG</TD> </TR> <TR> <TD>1</TD> <TD>900</TD> </TR> <TR> <TD>2</TD> <TD>5000</TD> </TR> <TR> <TD>3</TD> <TD>70000</TD> </TR> <TR> <TD>4</TD> <TD>9000</TD> </TR> <TR> <TD>5</TD> <TD>4000</TD> </TR> <TR> <TD>6</TD> <TD>3000</TD> </TR> <TR> <TD>7</TD> <TD>6055</TD> </TR> <TR> <TD>8</TD> <TD>10000</TD> </TR> <TR> <TD>9</TD> <TD>9000</TD> </TR> </TBODY> </TABLE> Tue, 04 Apr 2017 07:35:22 GMT TarunKumar 2017-04-04T07:35:22Z WANT TO FIND OUT M1 Balances https://communities.sas.com/t5/SAS-Programming/WANT-TO-FIND-OUT-M1-Balances/m-p/346947#M80051 <P>Hi,</P> <P>&nbsp;</P> <P>i have sas data set with the field Account number , ac_opn_date, and month on month balances . i want to create new dataset with Month one (m1) balances against the account number on the basis of account open date .</P> <P>&nbsp;</P> <P>for eg , for account number 1 account open date is Mar 16 then month balance is Apr_16 and for acccount nmer 2 account open date is Apr 2016 then month one balance is May_16, data is huge and i dont want to use if then else contition&nbsp;</P> <P>&nbsp;</P> <TABLE width="670"> <TBODY> <TR> <TD width="80">Account_no</TD> <TD width="88">Ac_opn_date</TD> <TD width="52">Mar_16</TD> <TD width="50">Apr_16</TD> <TD width="54">May_16</TD> <TD width="48">jun_16</TD> <TD width="44">jul_16</TD> <TD width="50">aug_16</TD> <TD width="51">Sep_16</TD> <TD width="49">Oct_16</TD> <TD width="53">Nov_16</TD> <TD width="51">Dec_16</TD> </TR> <TR> <TD>1</TD> <TD>01-Mar-16</TD> <TD>1000</TD> <TD>900</TD> <TD>2000</TD> <TD>1500</TD> <TD>3000</TD> <TD>6000</TD> <TD>6000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> </TR> <TR> <TD>2</TD> <TD>10-Apr-16</TD> <TD>0</TD> <TD>2000</TD> <TD>5000</TD> <TD>4000</TD> <TD>5000</TD> <TD>4000</TD> <TD>4000</TD> <TD>3000</TD> <TD>3000</TD> <TD>3000</TD> </TR> <TR> <TD>3</TD> <TD>15-May-16</TD> <TD>0</TD> <TD>0</TD> <TD>6000</TD> <TD>70000</TD> <TD>8000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> <TD>5000</TD> </TR> <TR> <TD>4</TD> <TD>16-Jun-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>8000</TD> <TD>9000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> <TD>6000</TD> </TR> <TR> <TD>5</TD> <TD>17-Jul-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>10000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> <TD>4000</TD> </TR> <TR> <TD>6</TD> <TD>18-Aug-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>3000</TD> <TD>3000</TD> <TD>30050</TD> <TD>30050</TD> <TD>30000</TD> </TR> <TR> <TD>7</TD> <TD>20-Sep-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>6000</TD> <TD>6055</TD> <TD>6055</TD> <TD>6000</TD> </TR> <TR> <TD>8</TD> <TD>16-Oct-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>10000</TD> <TD>10000</TD> <TD>9000</TD> </TR> <TR> <TD>9</TD> <TD>25-Nov-16</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>0</TD> <TD>9500</TD> <TD>9000</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>required data set is :</P> <P>&nbsp;</P> <TABLE width="168"> <TBODY> <TR> <TD width="80">Account_no</TD> <TD width="88">M1_AVG</TD> </TR> <TR> <TD>1</TD> <TD>900</TD> </TR> <TR> <TD>2</TD> <TD>5000</TD> </TR> <TR> <TD>3</TD> <TD>70000</TD> </TR> <TR> <TD>4</TD> <TD>9000</TD> </TR> <TR> <TD>5</TD> <TD>4000</TD> </TR> <TR> <TD>6</TD> <TD>3000</TD> </TR> <TR> <TD>7</TD> <TD>6055</TD> </TR> <TR> <TD>8</TD> <TD>10000</TD> </TR> <TR> <TD>9</TD> <TD>9000</TD> </TR> </TBODY> </TABLE> Tue, 04 Apr 2017 07:35:22 GMT https://communities.sas.com/t5/SAS-Programming/WANT-TO-FIND-OUT-M1-Balances/m-p/346947#M80051 TarunKumar 2017-04-04T07:35:22Z Re: WANT TO FIND OUT M1 Balances https://communities.sas.com/t5/SAS-Programming/WANT-TO-FIND-OUT-M1-Balances/m-p/346983#M80070 <PRE><CODE class=" language-sas">data have; input Account_no Ac_opn_date : date11. Mar_16 Apr_16 May_16 jun_16 jul_16 aug_16 Sep_16 Oct_16 Nov_16 Dec_16; format ac_opn_date date9.; cards; 1 01-Mar-16 1000 900 2000 1500 3000 6000 6000 5000 5000 5000 2 10-Apr-16 0 2000 5000 4000 5000 4000 4000 3000 3000 3000 3 15-May-16 0 0 6000 70000 8000 5000 5000 5000 5000 5000 4 16-Jun-16 0 0 0 8000 9000 6000 6000 6000 6000 6000 5 17-Jul-16 0 0 0 0 10000 4000 4000 4000 4000 4000 6 18-Aug-16 0 0 0 0 0 3000 3000 30050 30050 30000 7 20-Sep-16 0 0 0 0 0 0 6000 6055 6055 6000 8 16-Oct-16 0 0 0 0 0 0 0 10000 10000 9000 9 25-Nov-16 0 0 0 0 0 0 0 0 9500 9000 ; run; data want; set have; d=intnx('month',ac_opn_date,1); m1=vvaluex(cats(put(d,monname3.),'_',put(d,year2.))); drop d; run;</CODE></PRE> Tue, 04 Apr 2017 10:27:27 GMT https://communities.sas.com/t5/SAS-Programming/WANT-TO-FIND-OUT-M1-Balances/m-p/346983#M80070 Ksharp 2017-04-04T10:27:27Z