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 |
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.