I am very keen to learn whether I can handle or not such situations in SAS Base without using SAS IML;
Let's say I have the vector have
a b c d e f
1001 JPN 10,000 50% JPN 2,000
1001 EUR 12,648 100% EUR 3,000
1001 USD 15,997 50% USD 5,000
1001 JPN 20,233 20% JPN 8,000
1001 EUR 25,591 20% EUR 9,000
1001 USD 32,368 50% USD 4,000
1002 JPN 28,393 50% JPN 6,000
1002 EUR 24,906 100% EUR 4,000
1002 USD 21,847 50% USD 8,000
1002 TRY 19,164 20% JPN 6,000
1002 EUR 16,811 50% EUR 15,000
1002 USD 14,746 100% USD 52,000
1003 USD 10,000 50% XVN 8,000
%macro;
% let i = 1;
data want;
set have;
%do %while a[&i]=a[eval(&i+1)] ;
b = &i;
&i=eval(&i+1);
%end
%mend
What I would like to do is for a with b=e to take the difference of max(c) and max(f) and multiply this difference with d and then for each distinct a to sum these outcomes. This will be iterative. The table I compose here just a small representation of the case.
Thanks
Thank you for posting! I am assuming you want the max value when b = e, but I will give you two possible solutions just in case. Note that you only need to use proc sql once during any SQL calculation groups, but for clarity's sake we'll just do it twice.
Step 1: Get max values of c and f for when b = e
proc sql noprint;
create table maxes as
select b, e,
max(c) as max_c, max(f) as max_f
from have
where upcase(b) = upcase(e)
group by b, e
;
quit;
Step 2: Get the max values of when b = e into the table, sum up all the cases by a where b = e
proc sql noprint;
create table want as
select a, sum(result) as result
/* Get max values into the table.
Only interested in cases where a = b */
from(select st1.a, st1.b, st1.e,
max_c, max_f,
(max_c - max_f)*st1.d as result
from have as st1
INNER JOIN
maxes as st2
ON upcase(st1.b) = upcase(st2.b)
AND upcase(st1.e) = upcase(st2.e)
where upcase(st1.b) = upcase(st1.e) )
group by a
;
quit;
Now, if you want to do this for the table maximum of c and f, you can do it with macro variables, the table sorted by a, by-group processing, and a Sum Statement:
Step 1: Read maximum values of c and f into macro variables
proc sql noprint;
select max(c), max(f)
into :max_c, :max_f
from have;
quit;
Step 2: Sum the result for each a
data want;
set have;
by a;
where upcase(b) = upcase(e);
result+( (&max_c - &max_f)*d );
if(last.a) then do;
output;
call missing(result); *Reset the sum for the next a group;
end;
keep a result;
run;
I hope this helps! This is just one way you could go about solving the problem, but there are many other great ways. It all depends upon your goals, environment, and programming style.