turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Loop to perform calculations

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-27-2017 04:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

04-27-2017 04:52 PM

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

04-27-2017 04:38 PM

Is this what you are asking?

factor = (price * target) / current_cost;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

04-27-2017 04:44 PM

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

Solution

04-27-2017
05:14 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

04-27-2017 04:52 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

04-27-2017 05:03 PM

@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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

05-03-2017 12:02 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

05-03-2017 01:39 PM

JediApprentice wrote:

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

05-03-2017 02:25 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

05-03-2017 03:54 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

05-03-2017 05:01 PM

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

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

05-04-2017 10:44 AM

@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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

05-04-2017 11:20 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JediApprentice

05-04-2017 11:17 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

05-04-2017 12:04 PM

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