topic Re: Sum the rows based on variable values in SAS Programming

Sum the rows based on variable values

sameer112217 — Fri, 15 Sep 2017 15:23:22 GMT

I need help in find logic for this

Parentid Account_Name JAN_LY to DEC_LY JAN_CY to DEC_CY Condition Total_Values

101 MC donalds JAN_CY

103 Burger King FEB_CY

105 Dominos AUG_LY

Suppose if I have unique/single parentid, unique account_name for that and values in the column Jan LY to DEC CY. There might be 0 or missing values in any of these columns against the parentid number. Suppose if I have to sum the variable values starting from the values in the condition variable. CY is current year. LY is last year

Like for first row for parent id 101 based on condition I need to sum (JAN_CY - DEC CY) and put that in total values column. For parent id 103 I need to sum (FEB_CY-DEC_CY) and for 105 parentif I need to sm from (AUG_LY--DEC_CY). There may be missing values in these columns.

Sorry due to space constraints I am not able to write all columns and values. should i take condition column in macro variable and add like below example

total_values = sum (&condition_values - &reporting_month)..Reporting month can be from jan to dec 2017..

Re: Sum the rows based on variable values

Reeza — Fri, 15 Sep 2017 15:29:26 GMT

Please provide sample data and expected output.

Yes, your data is likely private, so make up fake data that is similar enough.

Re: Sum the variables rows based on variable values

ballardw — Fri, 15 Sep 2017 15:50:53 GMT

You really should provide some example data so code could be tested. And to show the desired results.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

There really is likely NO reason to bring macro variables into this.

Here is one approach with a smaller data set. Note the presence of an invalid condition.

data have;
   infile datalines dlm=',';
   informat Parentid $5. Accountname $10.  Jan_Ly Aug_Ly Dec_Ly  Jan_cy Aug_cy Dec_cy best6. Condition $6.;
   input Parentid  Accountname  Jan_Ly Aug_Ly Dec_Ly  Jan_cy Aug_cy Dec_cy Condition;
datalines;
101,McDonalds, 25, 10, , 17,27,37,Jan_cy
103,Burger King, 5,5,5,5,5,5,Aug_cy
105,dominos,1,2,3,4,5,6,AugLy
105,dominos,1,2,3,4,5,6,Aug_Ly
;
run;

data want;
   set have;
   array v Jan_Ly Aug_Ly Dec_Ly  Jan_cy Aug_cy Dec_cy;
   condition=upcase(condition);
   if vinarrayx(condition) then do i=1 to dim (v);
      if upcase(vname(v(i)))=condition then vindex=i;
      if vindex ge 0 then totalsum= sum(totalsum,v[i]);
   end;
run;

Re: Sum the variables rows based on variable values

novinosrin — Fri, 15 Sep 2017 16:25:08 GMT

@sameer112217 I agree with other responses. Please provdie sample INPUT, sample OUTPUT(Your req) and a brief explanation of the convert logic. Forgive me, I am too lazy to read very descriptive questions unless it mandates.

Re: Sum the variables rows based on variable values

sameer112217 — Fri, 15 Sep 2017 16:29:59 GMT

Sure will provide sample output and input

Re: Sum the rows based on variable values

HB — Fri, 15 Sep 2017 16:39:40 GMT

I agree about provide more data but i will assume data and proceed.

I assume you have a terrible data structure that is making the task much more complicated than it needs to be,

I did this:

data payments;
  input 
	@1 parentid 
	@5 account_name $12. 
	@17 date MMDDYY10.
	@28 amount 1.;
cards; 
101 mcdonalds   01/01/2017 1
103 burger king 01/01/2017 1
105 dominos     01/01/2017 1
101 mcdonalds   02/01/2017 1
103 burger king 02/01/2017 1
105 dominos     02/01/2017 .
101 mcdonalds   08/01/2017 1
103 burger king 08/01/2017 1
105 dominos     08/01/2017 1
101 mcdonalds   11/01/2017 1
103 burger king 11/01/2017 1
105 dominos     11/01/2017 1
101 mcdonalds   12/01/2017 0
103 burger king 12/01/2017 0
105 dominos     12/01/2017 0
;
run;


data triggers;
  input parentid trigger MMDDYY10.;
cards; 
101 01/01/2017 
103 02/01/2017 
105 08/01/2017
;
run;

* we can see that the sum of AMOUNT for 101 from trigger date 1/1/2017 to 12/1/2017 is 4;
* we can see that the sum of AMOUNT for 103 from trigger date 2/1/2017 to 12/1/2017 is 3;
* we can see that the sum of AMOUNT for 105 from trigger date 8/1/2017 to 12/1/2017 is 2;


proc sql;
create table payment_sums as
select
	payments.parentid,
	sum(payments.amount) as payment_sum
	from payments inner join triggers on payments.parentid = triggers.parentid 
	where payments.date between triggers.trigger and 21154
	group by payments.parentid
	order by payments.parentid;
quit;

Resulting in

The SAS System	
	
parentid	payment_sum
101	4
103	3
105	2

Perhaps that isn't what you want. Perhaps it is.

I'm not real good at SAS dates and someone else will be able to tell you how to do it better than 21154 if in fact this is what you want.

Re: Sum the rows based on variable values

sameer112217 — Tue, 19 Sep 2017 13:47:05 GMT

Hello Everyone,

I am coming up with exact structure of data for which I am struggling

data calculations;

id jan_LY feb_ly mar_ly apr_ly start_month$;

datalines;

1 100 . 300 400 jan_ly

2 100 . 300 400 feb_ly

3 100 . 300 400 mar_ly

4 100 . 300 400 jan_ly

5 100 . 300 400 mar_ly

;

run;

I want additonal variable called totalsum across all iteration where it should sum from the start_month value to apr_ly and want below output

id jan_LY feb_ly mar_ly apr_ly start_month totalsum

1 100 . 300 400 jan_ly 800

2 100 . 300 400 feb_ly 700

3 100 . 300 400 mar_ly 700

4 100 . 300 400 jan_ly 800

5 100 . 300 400 apr_ly 400

In short I do not want sum of the variables which is before the start_month values across each iteration. Feb_ly has blank values so it is .

I hope I am clear with the input and output as everyone wanted me to specify.

Re: Sum the rows based on variable values

Reeza — Tue, 19 Sep 2017 14:53:50 GMT

And a perfect example of why tidy data is important: http://vita.had.co.nz/papers/tidy-data.html

1. Declare an array of all your months.

2. Use a loop to loop through the months using VNAME to identify your starting point.

Sample code - untested and you'll need to type out your variable names and such.

array myMonths(*);

FOUND=0;

do i=1 to dim(myMonths);

_name = vname(myMonths(i);
if _name = start_month then found=1;


if found=1 then totalsum = sum(totalsum, myMonths(i));

end;

run;

Re: Sum the rows based on variable values

ShiroAmada — Wed, 20 Sep 2017 01:25:45 GMT

data want;

set have;

ARRAY Mval {4} $ ('Jan_ly',' Feb_ly','Mar_ly', 'Apr_ly'); *THIS IS CASE SENSITIVE;

ARRAY Mvar {4} jan_LY feb_ly mar_ly apr_ly;

do i=1 to dim(mval);

if start_month=mval(i) then

do x=i to dim(mval);

totalsum=sum(totalsum,mvar(x));

end;

run;

Re: Sum the rows based on variable values

sameer112217 — Wed, 20 Sep 2017 12:00:08 GMT

ShiroAmada and Reeza both yours answers are correct. I am deeply thankful to you. That was indeed high quality sas programing.

I got the solution and it worked.