Using do loop in calculation

[jo1]

Hi,  

I've overly simplify what I am trying to do so that I might be able to get some assistance on how to structure it and then I can add the complexity back in.  I need to find the max of a set of calculations that uses values for each day (which I have in another table) - and the number of days I have could be different depending on the data so I want a set of code that can incorporate various numbers of days.  

So as a simple example this is what I want it to do:

Proc sql;

data test;
attrib y length=3 format=best3.;
run;
Proc sql;
update test
set y = max(1*100,2*100,3*100);
run;

But without having to put the 1, 2, 3 in the calculation so that it would just size up to how many days I have - I was thinking that I would calculate how many days of data I had and then put that in a do loop - I'm still simplifying this as I'm just saying it's 3 days worth (will add the variable of total days later) - this is what I was thinking would work, but it is not.

data test;
attrib y length=3 format=best3.;
run;
%Macro Fmax;
Proc sql;
update test
%do i=1 %to 3;
set y = max(i*100)
%end;
%mend Fmax;
%Fmax;

Any help would be appreciated.

[SASKiwi]

You don't need to use macro and DO loops are much better in DATA steps:

data want;
  do i = 1 to 3;
    y = max(i * 100, i * 200, i * 300);
  end;
run;

[Tom]

I think you might have over simplified the problem so that your code makes no sense.

What is it this is varying?  If the number stored in the variable is different that does not normally require any change in the code.  That is why they are called variables.

[jo1]

Probably Tom,  so here is my actual code, but harder to give you the data set to make it work.  So this code works, as it calculates the value for each of the days for the time period in my table "AIM_ANALYSIS" - it has all the values pertaining to each day and time period of the sample (Day; Period; Load; Total_CL).  What I want is the max value (of this calculation) for each time period (across in this example the 7 days).  This code results in the table CALC_AIM which is exactly that (Period; Days (this is just the total days in the dataset); Per)   What I would like, is to not have this in long hand and have an equation for each day.... not sure that I'm explaining this any clearer.

%Let Aim = 1932;
Proc sql;
Create table CALC_AIM as
Select Period
,Count(Day) as Days
FROM LOAD_PERIOD
Group by Period;

Alter table CALC_AIM
Add Per num;

Update CALC_AIM
set Per =
max(
ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6)),

ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7)));
run;

[Tom]

Yikes.  Do you know what that is doing?

Let's see if a little formatting might make it more comprehendible.

max(
ifn(          (select Load     from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)>&AIM
   ,0,(&AIM - (select Load     from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1))
      /       (select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)
   )

Note:  You seem to have this repeated 7 times with only the value compare to DAY changind.  (Is the 7 fixed? Or does that vary also? )  What does the 7 mean? What does DAY mean?  What does LOAD mean.  What does TOTAL_CL mean?

Let's assume AIM is some target value you are trying to achieve?
So when LOAD has overshot you get a zero

Otherwise you take how far away you are from your aim and divide it by some number.  Perhaps to get a percentage of some kind?

So you want to find the one that undershot by the most?

Since you are basically calculating the same number for every day why not just do that up front?

select period,day,load,total_cl
     , case when (load>&aim) then 0 else (&aim-load)/total_cl end as want
from aim_analysis
 

And then just join that with your set of period,day values from the other table and take the MAX() of want?

[jo1]

Yes, for context, day original a date, basically I have 7 days of data, each with values for 48 x 30 min periods (this will be scaled up to 6mths or 1 year of data).  Correct I have repeated it 7 times.  Basically this is electricity load and we are trying to optimise the addition of Control Load - so I want to do the same calculation on all days (which could be up to 365) the max for each period across the data set will optmise the setting for that dataset. So AIM is the value at which the sum of the max's equals 1 or in which we have utilised 100% of the Load available.

Yep, I see how it could be done a lot simplier - so I have now this - just 3 simple steps.

%Let Aim = 1932;

/*Calculates the value for each day/period*/
Data Test_data 
set AIM_ANALYSIS;
Per = ifn(Load > &AIM,0,(&AIM - Load)/Total_CL);
run;

/*Calculates the max percentage setting for each period*/
Proc sql;
create table Test_Output as
select Period ,max(Per) as Max_value
from Test_data
group by Period;
run;

/*Sums the day*/
Proc sql;
create table Test_Total as
select
sum(Max_value)
from Test_Output;
run;

So I still don't have my answer to the question -  what value of AIM results in the final calculation (Test_Total) equaling 1.  So just looking into how to do that piece.

[Tom]

SAS has procedures for optimization.  You might want to see if you can use one of those.

Here is a way to just brute force trying multiple values of AIM.

Blow up you data to replicate it for each value of AIM you want to test.

Then perform your summary over the result to get one number per value of AIM.

Not sure what the valid range of vlaues of AIM are or what is a significant change. 

You start with trying 1000, 1100, 1200, ...

data per / view=per ; 
 set AIM_ANALYSIS;
 do aim=1000 to 2000 by 100;
   per = ifn(Load > AIM,0,(AIM - Load)/Total_CL);
   output;
 end;
run;

proc sql;
create table Test_Output as
select aim, sum(max_value) as want
from (
  select aim,period,max(Per) as Max_value
  from per
  group by aim,period
  )
group by aim
;
quit;

Even making it as a view might cause too much of strain on your system depending how large your analysis dataset is. In that case just run a few at a time.

[jo1]

Ok, in looking at the comments so far, I think I was making this much harder than it should be, It's just a table calculation and then a summary table to get the max.  In explanation I actually need to solve it for what value of "Aim" is required to have the max values for the period = 1 (or 100%).  So I think that is really the challenge and I was making the first bit harder because I had my eye on that.