BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

13 REPLIES 13
Astounding
PROC Star

Is this what you are asking?

 

factor = (price * target) / current_cost;

JediApprentice
Pyrite | Level 9

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

Astounding
PROC Star

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?

Reeza
Super User

@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;
JediApprentice
Pyrite | Level 9

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

Reeza
Super User

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

Astounding
PROC Star

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.

JediApprentice
Pyrite | Level 9

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.

 

 

 

 

 

Astounding
PROC Star

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.

 

 

 

JediApprentice
Pyrite | Level 9

@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

 

 

Astounding
PROC Star

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;

Reeza
Super User

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;
JediApprentice
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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