BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
J_CKY
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

LinusH
Tourmaline | Level 20

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
jwillis
Quartz | Level 8

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.);

ballardw
Super User

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.

Astounding
PROC Star

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2126 views
  • 8 likes
  • 6 in conversation