topic Re: How to lookup values from another table for calculation? in New SAS User

How to lookup values from another table for calculation?

BarryP — Fri, 02 Nov 2018 18:37:19 GMT

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!

Re: How to lookup values from another table for calculation?

PaigeMiller — Fri, 02 Nov 2018 18:41:28 GMT

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?

Re: How to lookup values from another table for calculation?

mkeintz — Fri, 02 Nov 2018 18:57:51 GMT

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?

Re: How to lookup values from another table for calculation?

BarryP — Fri, 02 Nov 2018 19:35:28 GMT

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

Re: How to lookup values from another table for calculation?

BarryP — Fri, 02 Nov 2018 19:39:38 GMT

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!

Re: How to lookup values from another table for calculation?

PaigeMiller — Fri, 02 Nov 2018 19:40:43 GMT

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?

Re: How to lookup values from another table for calculation?

Reeza — Fri, 02 Nov 2018 20:12:35 GMT

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!

Re: How to lookup values from another table for calculation?

BarryP — Fri, 02 Nov 2018 20:13:05 GMT

"...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!

Re: How to lookup values from another table for calculation?

PaigeMiller — Fri, 02 Nov 2018 20:19:26 GMT

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

Re: How to lookup values from another table for calculation?

Reeza — Fri, 02 Nov 2018 20:27:37 GMT

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.