topic Re: Sum based on column name and number in SAS Programming

Sum based on column name and number

Swapnil_21 — Thu, 03 Oct 2019 18:22:44 GMT

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

Re: Sum based on column name and number

Reeza — Thu, 03 Oct 2019 18:56:35 GMT

Are you required to maintain this data structure?
Did you create this table initially?
If you have your data in a long format it's easier to work with.

Re: Sum based on column name and number

Swapnil_21 — Thu, 03 Oct 2019 18:58:32 GMT

Yes this table is required to be maintained. Every month 2 new columns are
added to this table.

Re: Sum based on column name and number

Tom — Thu, 03 Oct 2019 19:21:05 GMT

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;

Re: Sum based on column name and number

Reeza — Thu, 03 Oct 2019 19:33:53 GMT

Out of curiosity what would the sum of a 'balance' field represent from a business context?

In general, I keep $1000 in my account as balance. Not sure how the sum of that, $12,000 is a useful metric so I'm curious as to how this would be used...

Balance typically means the outstanding amount as of a particular date, but I suppose it could have a different interpretation.

Re: Sum based on column name and number

hashman — Fri, 04 Oct 2019 03:46:30 GMT

@Swapnil_21:

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.