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
- /
- Data Management
- /
- Forum
- /
- Creating a formula column based on available infor...

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2014 10:04 PM

Hi,

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

and suppose I have the following table:

A | B | C | D |
---|---|---|---|

a1 | b1 | ||

a2 | c2 | d2 |

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

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

12-08-2014 10:31 PM

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;

All Replies

Solution

12-08-2014
10:31 PM

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

12-08-2014 10:31 PM

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;

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

12-08-2014 11:39 PM

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

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

12-09-2014 12:43 AM

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;

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

12-09-2014 01:29 AM

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.