BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TarunKumar
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

1 REPLY 1
Ksharp
Super User
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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