DATA Step, Macro, Functions and more

Loop to perform calculations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Loop to perform calculations

What I need to do is, figure out what to multiply the Current_Cost by in order to reach the value you would get by discounting the Price by the target. And be able to loop through all of them and calculate this. Here is sample data:

 

 

data have;
  infile datalines;
  format Price dollar6.2 Current_Cost dollar6.2 Target percent8.2;
  input Price Current_Cost Target;
  datalines;
98 40 .7641
56 30 .7621
88 20 .5036
20 10 .2022
30 23 .2491
55 45 .8612
;;
run;

Accepted Solutions
Solution
‎04-27-2017 05:14 PM
Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

If you add that statement right after the INPUT statement, it will be applied to each observation.  Did you have something more complex in mind?

View solution in original post


All Replies
Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

Is this what you are asking?

 

factor = (price * target) / current_cost;

Frequent Contributor
Posts: 123

Re: Loop to perform calculations

Posted in reply to Astounding

@Astounding Yes exaclty, just not sure how to loop through the dataset

Solution
‎04-27-2017 05:14 PM
Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

If you add that statement right after the INPUT statement, it will be applied to each observation.  Did you have something more complex in mind?

Super User
Posts: 19,768

Re: Loop to perform calculations

Posted in reply to JediApprentice

@JediApprentice SAS automatically loops through each row in a dataset. It's how it processes data, line by line. This makes it harder to look forward (ie creating lead variables) but it's more like Excel in some ways. It also makes it easy to process large data because it simply goes through it line by line, doesn't matter how much for most processes. Procedures that need memory can run into issues, ie regressions but data management stuff is not affected.

 

For example this creates a new dataset and a new variable - BMI based on the SASHELP dataset. Because SAS ships with this by default you should be able to run the code directly.

 

data example;
set sashelp.class;

BMI = 703 * weight/ (height ** 2);

run;
Frequent Contributor
Posts: 123

Re: Loop to perform calculations

@Reeza @Astounding Well it turns out this is actually more complex then I thought. I am given just one Target percentage. I have to add up the total for Price, then calculate a single factor to multiply each Current_Cost by to add up to a new cost that would be the target discounted off of the total Price.

Super User
Posts: 19,768

Re: Loop to perform calculations

Posted in reply to JediApprentice

JediApprentice wrote:

@Reeza @Astounding Well it turns out this is actually more complex then I thought. I am given just one Target percentage. I have to add up the total for Price, then calculate a single factor to multiply each Current_Cost by to add up to a new cost that would be the target discounted off of the total Price.


ok.  Have you figured this out or do you still need help? If you need help, post sample data and output.

Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

As Reeza said, posting a small amount of sample data is essential.  For example, when you are given a single Target percentage, we don't know whether you intend to hard-code that into your program or whether you get a separate data set (with just one piece of information) that needs to be utilized in your program.  It's not that the programming will be that difficult, it's that we need to be able to visualize the pieces that we'll be working with and what we need to produce.

Frequent Contributor
Posts: 123

Re: Loop to perform calculations

Posted in reply to Astounding

Okay, so here is the situation. Here's the sample data. I goofed up on explaining this before.

 

 

data have;
  infile datalines;
  format Price dollar7.2 Current_Cost dollar7.2 Target percent8.2 MAC_Price dollar7.2 Quantity 2.;
  input Price Current_Cost Target MAC_Price Quantity;
  datalines;
100 40 .7641 1.33 30
300 100 .7641 3.33 30
325 25 .7641 0.83 30
400 35 .7641 1.17 30
100 25 .7641 0.83 30
150 1.50 .7641 0.05 30
350 100 .7641 3.33 30
425 25 .7641 0.83 30
175 55 .7641 1.83 30
225 195 .7641 6.50 30
;;
run;

 

And this is what I want to see:

 

data want;
  infile datalines;
  format Price dollar7.2 Current_Cost dollar7.2 Target percent8.2 MAC_Price dollar7.2 Quantity 2. Factor 3.2;
  input Price Current_Cost Target MAC_Price Quantity Factor;
  datalines;
100 40 .7641 1.33 30  1.11
300 100 .7641 3.33 30 1.11
325 25 .7641 0.83 30 1.11
400 35 .7641 1.17 30 1.11
100 25 .7641 0.83 30 1.11
150 1.50 .7641 0.05 30 1.11
350 100 .7641 3.33 30 1.11
425 25 .7641 0.83 30 1.11
175 55 .7641 1.83 30 1.11
225 195 .7641 6.50 30 1.11
;;
run;

 

The total of the Prices multiplied by (1-Target) gives us the value we want to get closest to by totalling the values we would get by multiplying MAC_Price*Quantity*Factor for each observation. So for this example:

 

Total of Prices = $2550.00

  -> 2550*(1-76.41%) = 601.50 value we want to match with the value we get from totalling the values from this calculation:

  -> MAC_Price*Quantity*Factor  . But the tricky thing is solving for Factor. The way I did

       this one was just by plugging in values in excel and came to the closest value of $667.67 by using factor of 1.11. I didn't want to go        past the second decimal place. 601.50 is also rounded I believe, should be 601.54

 

 

I hope this is making sense, sorry to confuse you guys before.

 

 

 

 

 

Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

Don't worry about it.  We are easily confused.  Smiley Happy

 

SAS has many ways to get the sum of all the prices.  Here's one approach that does it all in a single step (although I'm not certain that the calculations at the end are correct).

 

data want;

do until (done1);

   set have end=done1;

   total_of_prices + price;

end;

do until (done2);

   set have end=done2;

   * Maybe, not sure here:  factor = total_of_prices * (1 - percent) / current_cost;

   output;

end;

run;

 

The top loop reads through the data, computing the total of all the prices.  Then the bottom loop re-reads the same observations, and uses the total of the prices in further calculations.

 

 

 

Frequent Contributor
Posts: 123

Re: Loop to perform calculations

Posted in reply to Astounding

@Astounding Okay, I think I see what you're doing here. However, what I want is ONE value for factor that applies to all observations to add up to a total that is as close as we can get to the value we get by calculating Total of Prices*(1-Percent), which in this case is $663.00 In my example I ended up with 1.11 to get closest to that total. The top loop is perfect. But factor should be a constant.

 

MAC_Price*Quantity*Factor = $1.33*30*1.11 = $44.40

MAC_Price*Quantity*Factor = $3.33*30*1.11 = $111.00

MAC_Price*Quantity*Factor = $0.83*30*1.11 = $27.75

MAC_Price*Quantity*Factor = $1.17*30*1.11 = $38.85

MAC_Price*Quantity*Factor = $0.83*30*1.11 = $27.75

MAC_Price*Quantity*Factor = $0.05*30*1.11 = $1.67

MAC_Price*Quantity*Factor = $3.33*30*1.11 = $111.00

MAC_Price*Quantity*Factor = $0.83*30*1.11 = $27.75

MAC_Price*Quantity*Factor = $1.83*30*1.11 = $61.05

MAC_Price*Quantity*Factor = $6.50*30*1.11 = $216.45

                                                               Total = $667.67 (which is as close we can get with precison of 2 decimal places)

 

The output for what you had looks like this (with different factors). factors are in the first column

 

Factors.PNG

 

 

Super User
Posts: 5,496

Re: Loop to perform calculations

Posted in reply to JediApprentice

OK, getting closer.  This won't be exactly right, since it doesn't use Percent.  But should be close enough that you can fix it from here.

 

data want;

do until (done1);

   set have end=done1;

   total_of_prices + price;

   total_pq + mac_price * quantity;

end;

factor = total_of_prices / total_pq;  * or should that be total_pq / total_of_prices ?;

do until (done2);

   set have end=done2;

   new_field = mac_price * quantity * factor;

   output;

end;

run;

Super User
Posts: 19,768

Re: Loop to perform calculations

Posted in reply to JediApprentice

Well, this is time to dig out your basic algebra book. 

 

Z = sum (x*y*CONSTANT) = CONSTANT * sum(x*y)

Constant = Z / sum(x*y)

 

Which means you can factor out your 'factor 'and calculate it via division if you know it's supposed to be Z and you know x/y you can solve this equation.

 

I still don't understand what you're doing really, but given what you posted this derives the values. It's not the most efficient, but it's clear and understandable. I don't know what the 667 number comes from or what it represents, the factor ends up being much smaller than 1.1 to match the number indicated, 601.55 which is what I get. 

 

data have;
	infile datalines;
	format Price dollar7.2 Current_Cost dollar7.2 Target percent8.2 MAC_Price 
		dollar7.2 Quantity 2.;
	input Price Current_Cost Target MAC_Price Quantity;
	datalines;
100 40 .7641 1.33 30
300 100 .7641 3.33 30
325 25 .7641 0.83 30
400 35 .7641 1.17 30
100 25 .7641 0.83 30
150 1.50 .7641 0.05 30
350 100 .7641 3.33 30
425 25 .7641 0.83 30
175 55 .7641 1.83 30
225 195 .7641 6.50 30
;
	;
	run;




proc sql ;
	create table summary as select sum(price) as total_price, mean(target) as 
		mean_target, sum(mac_price*quantity) as total_weighted, sum(price*(1-target)) 
		as target_price, calculated target_price / calculated total_weighted as 
		factor from have;
quit;

proc sql noprint;
	select factor into :factor from summary;
quit;

data want;
	set have;
	factor=&factor;
	value=mac_price*quantity*factor;
run;

proc means data=want sum;
	var value;
run;
Frequent Contributor
Posts: 123

Re: Loop to perform calculations

@Reeza Yup that is exactly what I'm looking for. Sorry about the 667 number. It was an example I was messing around with where the total current_cost was $663.00 and Target was 74.00. I got the numbers mixed up. Thank you both for your help! Much appreciated.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 210 views
  • 4 likes
  • 3 in conversation