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.
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.
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 ...
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.
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.);
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.