Hi,
I have data as below.
Depending on the Month of account opening, I need to create new variable called Required_Sum as Opening Balance + sum of individual months greater than opening month
Desired is :
For Cid=1 Account is opened in April month so Required_sum=opening_Balance_may19 + Balance_May19+ Balance_June19.... till end -----------> Required_Sum =300+400 +200+150+80 = 1130
For Cid=2 Required_Sum=100+100+40+60=300
Cid | Account_open_date | opening_Balance_apr19 | Balance_apr19 | Opening_balance_may19 | Balance_may19 | Opening_balance_jun19 | Balance_jun19 | Opening_balance_jul19 | Balance_jul19 | Opening_balance_aug19 | Balance_aug19 |
1 | 08-May-19 | . | . | 300 | 400 | 100 | 200 | 100 | 150 | 170 | 80 |
2 | 13-Jun-19 | . | . | . | . | 100 | 100 | 100 | 40 | 100 | 60 |
2 | 16-Jul-19 | . | . | . | . | . | 150 | 100 | 100 | 100 | 100 |
\
Above is just sample data and there are columns from Apr15 till Sep19. How do I run it through macro and get only the first month opening balance. Any help is really appreciated a lot
Not sure I get what you are trying to do, but shouldn't columns that represent dates before the starting date just by missing? In which case why not just sum ALL of the columns and ignore the value of the starting date.
How many numeric variables do you have? Make sure to count DATE variables since SAS stores dates as numbers.
How many of them need to contribute to the SUM?
data want ;
set have;
sum=sum(of _numeric_) - sum(of account_open_date sum);
run;
First, tell whomever has designed this "data structure" and intends to keep and maintain it this way: "Welcome to data processing and maintenance nightmare".
Having said that, you can do what you need this way:
data have ;
input Cid
Account_open_date :date.
opening_Balance_apr19
Balance_apr19
Opening_balance_may19
Balance_may19
Opening_balance_jun19
Balance_jun19
Opening_balance_jul19
Balance_jul19
Opening_balance_aug19
Balance_aug19
;
format Account_open_date yymmdd10. ;
cards ;
1 08-May-19 . . 300 400 100 200 100 150 170 80
2 13-Jun-19 . . . . 100 100 100 40 100 60
2 16-Jul-19 . . . . . 150 100 100 100 100
;
run ;
data want (drop = _:) ;
set have ;
array nn _numeric_ ;
do over nn ;
if cmiss (nn) then continue ;
_open = lowcase (vname (nn)) =: "opening_balance" ;
_seq = sum (_seq, _open) ;
if _seq = 1 or (_seq and not _open) then Required_Sum = sum (required_sum, nn) ;
end ;
run ;
Kind regards
Paul D.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.