Hi,
Can anyone help re write my case statement please so it works?
proc sql;
create table adjusted_movement as
select *,
case when ltd_allocations gt runnning_sum then 'current' else if ltd_Allocations + weekly_movement gt dealt_amount
then running_sum - ltd_allocations else weekly_movement end as adjusted_movement
from
Allocate_rates_LTD_Allocations;
quit;
Hi,
proc sql;
create table adjusted_movement as
select *,
case when ltd_allocations gt runnning_sum then 'current'
when ltd_Allocations + weekly_movement gt dealt_amount then running_sum - ltd_allocations
else weekly_movement end as adjusted_movement
from Allocate_rates_LTD_Allocations;
quit;
I now get this error message?
540 proc sql;
541 create table adjusted_movement as
542 select*,
543 case
544 when ltd_allocations gt running_sum then 'current'
545 when ltd_allocations + weekly_movement gt dealt_amount_ then running_sum - ltd_allocations
546 else weekly_movement end as adjusted_movement
547 from
548 Allocate_rates_LTD_Allocations;
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
549 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Yes, thats because the logic of the second when results in a numeric value, the first 'current'. You need to decide what you want the outcome to be, for instance, if the column is to be text then:
proc sql;
create table adjusted_movement as
select *,
case when ltd_allocations gt runnning_sum then 'current'
when ltd_Allocations + weekly_movement gt dealt_amount then put(running_sum - ltd_allocations,best.)
else put(weekly_movement,best.) end as adjusted_movement
from Allocate_rates_LTD_Allocations;
quit;
If its to be numeric then you can't have current, maybe:
proc sql;
create table adjusted_movement as
select *,
case when ltd_allocations gt runnning_sum then 99999
when ltd_Allocations + weekly_movement gt dealt_amount then running_sum - ltd_allocations
else weekly_movement end as adjusted_movement
from Allocate_rates_LTD_Allocations;
quit;
Try:
proc sql;
create table adjusted_movement as
select *,
case when ltd_allocations gt runnning_sum then 'current' when ltd_Allocations + weekly_movement gt dealt_amount
then running_sum - ltd_allocations else weekly_movement end as adjusted_movement
from
Allocate_rates_LTD_Allocations;
quit;
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.