WANT TO FIND OUT M1 Balances

[TarunKumar]

Hi,

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 .

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 

Account_no	Ac_opn_date	Mar_16	Apr_16	May_16	jun_16	jul_16	aug_16	Sep_16	Oct_16	Nov_16	Dec_16
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

required data set is :

Account_no	M1_AVG
1	900
2	5000
3	70000
4	9000
5	4000
6	3000
7	6055
8	10000
9	9000

[Ksharp · Accepted solution]

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;