SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a formula column based on available information

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Creating a formula column based on available information

Hi,

suppose I have the following equation:  R = A*B, and B = C/D

and suppose I have the following table:

ABCD
a1b1
a2c2d2

In the first line I can directly calculate R1 = a1*b1, but in the second line I need to  R2 = a2*c2/d2,

so how is it possible to write a code that will choose the right formula depending on the available information?

Thank you

ps: in case all variables are available, choose the "straightest" method, i.e  R=A*B


Accepted Solutions
Solution
‎12-08-2014 10:31 PM
Trusted Advisor
Posts: 1,204

Re: Creating a formula column based on available information

Hi,

Try this.

data have;

input A B C D;

datalines;

2 5 . .

1 . 10 5

;

data want;

set have;

R=a*coalesce(b,c/d);

run;

View solution in original post


All Replies
Solution
‎12-08-2014 10:31 PM
Trusted Advisor
Posts: 1,204

Re: Creating a formula column based on available information

Hi,

Try this.

data have;

input A B C D;

datalines;

2 5 . .

1 . 10 5

;

data want;

set have;

R=a*coalesce(b,c/d);

run;

Super Contributor
Posts: 413

Re: Creating a formula column based on available information

Hi stat@sas, ran your code and it was good, thank you!

Could you please also show me how to achieve the same result using the IF THEN ELSE procedures because if my actual case will have more variables and more complex equations I guess it will be too messy to put everything into a single COALESCE?

Thank you

Trusted Advisor
Posts: 1,204

Re: Creating a formula column based on available information

Hi,

Below is an example using IF THEN ELSE statement.

data want;

set have;

if b ne . then R=a*b;

else R=a*c/d;

run;

Super User
Posts: 17,912

Re: Creating a formula column based on available information

Since you'll end up hard coding things not necessarily. If you have multiple formulas/conditions I'd probably calculate them all, but then use a coalesce to determine which is the final one to keep. The issue then becomes how to keep your log clean, if that's a requirement.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 311 views
  • 6 likes
  • 3 in conversation