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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.