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..
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;
Please provide sample data and expected output.
Yes, your data is likely private, so make up fake data that is similar enough.
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;
@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.
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.
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.
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;
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: