How to lookup values from another table for calculation?

[BarryP]

Hi, I'm looking for a more efficient way to do this than what I've come up with.  I need to multiply QUANTITY by COST, but I want COST to exist in a separate table so that that calculations will carry through when they are updated.  

Table_1:

ACCOUNT     QUANTITY
Bob                        5
Louis                      10
Sarah                     12

Table_2:

COST
5,200


Table_I_Want:

ACCOUNT     QUANTITY      COST     TOTAL
Bob                       5                5,200       26,000
Louis                     10              5,200       52,000

Sarah                    12              5,200       62,400




Right now I'm creating a lookup field for each table and then merging the tables together, but I'm assuming there is a more efficient way.

Thanks!

[PaigeMiller]

Well, if you want to keep the quantity in a separate table from the cost, then you will have to merge the two tables together somehow.

So, is there a more efficient way? I don't think so, unless you are doing extremely inefficient merging now. Can you show us how you are doing this today?

[BarryP · Accepted solution]

This is what I am currently doing.

Table_1:

ACCOUNT     QUANTITY  LookUpKey
Bob                        5           Service_Key
Louis                      10         Service_Key
Sarah                     12         Service_Key

Table_2:

COST     LookUpKey
5,200       Service_Key


SAS Code:  

Data Table_I_Generate;

     merge Table_1 Table_2;

     by LookUpKey;

run;


Table_I_Generate:


ACCOUNT     QUANTITY  LookUpKey      COST 
Bob                        5           Service_Key     5,200
Louis                      10         Service_Key     5,200
Sarah                     12         Service_Key     5,200



Code:

data Table_With_Total

     set Table_I_Generate(drop=LookUpKey);

     Total = Quantity * Cost;

     Drop 

[PaigeMiller]

That's what is required. I doubt it could be made simpler.

Now if you have a much larger data sets, it probably will take more time to do the merge ... what specifically is the problem you are trying to eliminate or reduce?

[BarryP]

 "...what specifically is the problem you are trying to eliminate or reduce?"


Nothing in particular.  As I'm learning to code I'm trying to eliminate as many workarounds and be as efficient as possible while avoiding bad habits.

Thanks a lot for the feedback, it was very helpful!

[Reeza]

In general, when you start out its common to relate it to what you know. For SAS, the key idea to remember at all times, it's really only seeing one line at a time and then goes through each line in order. If that value isn't available in that row, then you need to get it there somewhere. Trying to do things as in Excel means your programs will be inefficient in SAS because there's usually a different way that can be handled a bit easier. Common tasks (transpose) have PROCS that would be otherwise almost impossible in Excel, to streamline at least.

[mkeintz]

You present the problem as one in which the cost "table" has a single value.  After all you are using it as a constant for all three records in the quantity table.  Is that true, or can you have differing table_2 costs for differing accounts in table_1?

[BarryP]

Yes, it would be a constant value for every record.  It would not change from one record to the next.

When I do this in Excel, I just reference the cell where it exists in the calculation.  For example, if it is in cell A1 I would multiply each row by $A$1.  

I've got about 13 years experience doing pretty involved calculations in Excel but have only been programming in SAS for ~6 months, so I'm still getting used to manipulating databases.  It looks like there's some subtle but important differences.

Thanks!

[Reeza]

If it's a constant pull that into a macro variable and use it in your calculations. You can also keep it in a view so it is up to date every time you 'look' at the table. If you're not familiar with views, it may be beneficial in this case. 

*create macro variable with cost;

proc sql noprint;
select cost into :cost_value from table_2;
quit;

data want / view=want;
set have;

Total = &cost_value * Quantity;

run;

---

@BarryP wrote:

Yes, it would be a constant value for every record.  It would not change from one record to the next.

When I do this in Excel, I just reference the cell where it exists in the calculation.  For example, if it is in cell A1 I would multiply each row by $A$1.  

I've got about 13 years experience doing pretty involved calculations in Excel but have only been programming in SAS for ~6 months, so I'm still getting used to manipulating databases.  It looks like there's some subtle but important differences.

Thanks!

---

[PaigeMiller]

---

@BarryP wrote:

Yes, it would be a constant value for every record.  It would not change from one record to the next.

When I do this in Excel, I just reference the cell where it exists in the calculation.  For example, if it is in cell A1 I would multiply each row by $A$1.  

---

SAS can't do things the way Excel does things. You'll just have to learn to think differently.