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
- /
- How can I simplify numeric IF-THEN-ELSE conditions...

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

06-24-2014 05:39 AM

I am looking a better way to code the following IF-THEN-ELSE logic.

IF COLUMN_A = 0 THEN COLUMN_B = 0; | |

ELSE IF 0 < COLUMN_A <= 1000000 THEN COLUMN_B = 1000000; | |

ELSE IF 1000000 < COLUMN_A <= 2000000 THEN COLUMN_B = 2000000; | |

ELSE IF 2000000 < COLUMN_A <= 5000000 THEN COLUMN_B = 5000000; | |

ELSE IF 5000000 < COLUMN_A <= 10000000 THEN COLUMN_B = 10000000; | |

ELSE COLUMN_B = 10000000; |

The above is a simplified logic, I have go way more level to do in ELSE-IF statements. It would be great if someone can shed a light on a better way in coding this rather keep using ELSE-IF and type manually.

Cheers.

Accepted Solutions

Solution

06-24-2014
12:23 PM

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

Posted in reply to jwillis

06-24-2014 12:23 PM

Better is INVALUE format

proc format;

invalue mynum (min=8 max=32)

0 = 0

0 - 1000000 = 1000000 /* 0.00000000......1 to 1000000 */

1000000 <- 2000000 = 2000000;

other = 1000000;

run;

and

b= input(a,MYnum);

Unless you want to create character values for b.

All Replies

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

Posted in reply to J_CKY

06-24-2014 06:10 AM

Well, there are other methods to do blocks of if. The first two that spring to mind are select-when for datastep, and case when for SQL (note that there are several forms of select and case):

data have;

set want;

select;

when(column=0) then ...;

when(1<=column<=2) then ...;

otherwise...

end;

/* Or */

select (column);

when(0) then ...;

when(1,2) then ...;

otherwise...

end;

...

proc sql;

create table want as

select *,

case when column=0 then ...

when 1<=column<=2 then ...

else . end as ....,

case column when 0 then ...

when 1 or 2 then ....

else ...

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

06-24-2014 08:48 AM

A third one is to use a look-up table. This way, you can drive this logic by data, not by hard coded statements.

You can us SQL inner/left join with between and logic to get column_b values.

Data never sleeps

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

Posted in reply to J_CKY

06-24-2014 08:59 AM

A proc format will also work. (not tested)

proc format;

value mynum (min=8 max=32)

0 = 0

0 - 1000000 = 1000000 /* 0.00000000......1 to 1000000 */

............

other = 1000000;

run;

b=put(A,mynum.);

Solution

06-24-2014
12:23 PM

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

Posted in reply to jwillis

06-24-2014 12:23 PM

Better is INVALUE format

proc format;

invalue mynum (min=8 max=32)

0 = 0

0 - 1000000 = 1000000 /* 0.00000000......1 to 1000000 */

1000000 <- 2000000 = 2000000;

other = 1000000;

run;

and

b= input(a,MYnum);

Unless you want to create character values for b.

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

Posted in reply to J_CKY

06-24-2014 09:13 AM

Well, you do have to consider what should happen if COLUMN_A is less than 0 (missing values, included). Here, you give COLUMN_B the final value of 10,000,000.

Assuming you can't find a mathematical pattern to apply a short formula, you can cut out half of the comparisons pretty easily.

if column_a < 0 then column_b = .;

else if column_a = 0 then column_b = 0;

else if column_a <= 1000000 then column_b = 1000000;

else if column_a <= 2000000 then column_b = 2000000;

else if column_a <= 5000000 then column_b = 5000000;

else column_b = 10000000;

If you are looking for these exact comparisons, where for most ranges the upper value of column_a gets assigned to column_b, there may be a shorter way that applies a formula. But I didn't want to approach it that way unless I knew that's what you wanted.