Sum the rows based on variable values

[sameer112217]

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..

[Reeza]

Please provide sample data and expected output. 

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

[ballardw]

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-dat... 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;
      

[novinosrin]

@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.

[sameer112217]

Sure will provide sample output and input

[HB]

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.

[sameer112217]

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.

[Reeza]

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;

[ShiroAmada · Accepted solution]

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;

end;

run;

[sameer112217]

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.