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