topic Re: Using formulas to sum values between rows in SAS Programming

Using formulas to sum values between rows

linaibrou — Mon, 01 May 2023 22:18:28 GMT

I have a dataset below. The formula is just summing each value in the MemberCount column. I thought about just creating a new variable for each value but is there an easier way to do this? Thanks!

IndicatorKey	MemberCount	Formula	Calc
201820_20	323	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	1800
201822_20	560	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	1800
201824_20	257	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	1800
201828_20	660	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	1800

Re: Using formulas to sum values between rows

mkeintz — Mon, 01 May 2023 22:26:28 GMT

Your formulas all appear to be identical, so I see no reason to parse the formula row by row instead of making a new variable with the single, repeated, formula in your program code.

But also, I do not understand your formula, which seems to be 5 colon-separated expressions. How would they be interpreted?

Re: Using formulas to sum values between rows

linaibrou — Mon, 01 May 2023 22:42:57 GMT

This is just one formula I have in a large dataset where the formula variable includes like 50 different formulas. I just made this table to simplify my question. I have SAS correctly referencing all of the other formulas using call execute but this one is giving me trouble. I want SAS to reference the correct value. "201820_20:MemberCount" is referencing the value of MemberCount for the IndicatorKey=201820_20 which would be 323 (the first row) and so on and so forth. So the final column Calc should just add up each row to 1800 (323+560+257+660).

Re: Using formulas to sum values between rows

Tom — Tue, 02 May 2023 02:13:46 GMT

If you want to aggregate by the value of variable like INDICATORKEY then just tell SAS to do that directly. No need for a "formula".

proc means ;
   class indicatorkey;
   var membercount;
run;

Re: Using formulas to sum values between rows

mkeintz — Tue, 02 May 2023 02:28:13 GMT

If the example you provide is representative, then you want the sum of calc over all obs that have the same formula. And for each distinct formula, the summation includes all the corresponding calc values and nothing else.

And if the data are already physically grouped by formula, then this would work:

data want;
  do until (last.formula);
    set have;
    by formula notsorted;
    calc=sum(calc,membercount);
  end;
  do until (last.formula);
    set have;
    by formula notsorted;
    output;
  end;
run;

Now this does create a new variable, but it's a trivial effort.

Re: Using formulas to sum values between rows

Ksharp — Tue, 02 May 2023 04:16:45 GMT

data have;
infile cards expandtabs truncover;
input IndicatorKey :$20.	MemberCount	Formula	 $100.;
cards;
201820_20	323	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	
201822_20	560	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	
201824_20	257	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	
201828_20	660	(201820_20:MemberCount+201822_20:MemberCount+201824_20:MemberCount+201828_20:MemberCount)	
;
data want;
 if _n_=1 then do;
   if 0 then set have(rename=(MemberCount=_MemberCount));
   declare hash h(dataset:'have(rename=(MemberCount=_MemberCount))');
   h.definekey('IndicatorKey');
   h.definedata('_MemberCount');
   h.definedone();
 end;
set have;
want=0;
do i=1 to countw(Formula,'_','kd');
 if h.find(key:scan(Formula,i,'_','kd'))=0 then want+_MemberCount;
end;
drop i _MemberCount;
run;

Re: Using formulas to sum values between rows

linaibrou — Tue, 02 May 2023 16:09:29 GMT

Thank you! This worked!

Re: Using formulas to sum values between rows

Tom — Tue, 02 May 2023 16:30:25 GMT

Yikes. What a complicated process for such a simple task.

proc sql  ;
create table want2 as
  select *
       , sum(membercount) as want
  from have
  group by formula
  order by 1
;
quit;

Here is result of comparing WANT to WANT2.

Observation Summary                                                                                                                 
                                                                                                                                    
Observation      Base  Compare                                                                                                      
                                                                                                                                    
First Obs           1        1                                                                                                      
Last  Obs           4        4                                                                                                      
                                                                                                                                    
Number of Observations in Common: 4.                                                                                                
Total Number of Observations Read from WORK.WANT: 4.                                                                                
Total Number of Observations Read from WORK.WANT2: 4.                                                                               
                                                                                                                                    
Number of Observations with Some Compared Variables Unequal: 0.                                                                     
Number of Observations with All Compared Variables Equal: 4.                                                                        
                                                                                                                                    
NOTE: No unequal values were found. All values compared are exactly equal.

Re: Using formulas to sum values between rows

Ksharp — Wed, 03 May 2023 11:29:09 GMT

Tom,
I think formula is changing dynamically , not just for all the obs. Maybe OP should explain more details about his question?