@Quentin I am attaching what I think now works and does what it should Obviously, it was not possible for the code below to work without your code and the correct answer. I will still leave the working code below if anyone else wants to do what I am trying to do (which probably is odd as well lol). Feel free to try it and let me know if you think it is correct or can be improved. P.S. I am unable to use the SCAN for _ (underscore) as some Tiers could have multiples. Thanks again for your help. %macro exception(finish);
%let len = length(&finish);
cats(substr(&finish,1,&len-1),substr(&finish,&len)+1)
%mend exception;
proc sql;
create table data_in (
STUDENT_NO NUM(8),
TEST_SCORE NUM(20),
TIER CHAR(50),
EXCEPTION_CD CHAR(10)
);
insert into data_in (STUDENT_NO, TEST_SCORE, TIER, EXCEPTION_CD)
values(1, 70, 'Tier_3', ' ')
values(2, 80, 'Test_Tier_2', '8')
values(3, 90, 'Tier_1', '8')
values(4, 65, 'Tier_0', ' ')
;
quit;
data want;
set data_in;
select (EXCEPTION_CD);
when ('8') EXCEPTION_TIER = %exception(TIER);
otherwise EXCEPTION_TIER = TIER;
end;
run;
... View more