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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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