DATA Step, Macro, Functions and more

WANT TO FIND OUT M1 Balances

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

WANT TO FIND OUT M1 Balances

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

Accepted Solutions
Solution
‎04-04-2017 07:02 AM
Super User
Posts: 9,687

Re: WANT TO FIND OUT M1 Balances

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;

View solution in original post


All Replies
Solution
‎04-04-2017 07:02 AM
Super User
Posts: 9,687

Re: WANT TO FIND OUT M1 Balances

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;
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 137 views
  • 0 likes
  • 2 in conversation