Hello All,
Need assistance with an issue I am unable to find a solution for. Below are the Macro and sample Data Step I have.
%macro exception(finish);
%do i=%substr(&finish, %length(&finish), 1) %to %eval(%substr(&finish, %length(&finish), 1)+1);
%let i=%eval(&i+1);
%put %substr(&finish, 1, %eval(%length(&finish)-1))&i;
%end;
%mend;
/*Macro would increment the Tier by 1, the condition in DataStep is true*/
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, 'Tier_2', '8')
values(3, 90, 'Tier_1', '8')
values(4, 65, 'Tier_0', '')
;
quit;
data data_in;
set data_in;
select (EXCEPTION_CD);
when ('8') EXCEPTION_TIER = %exception(TIER);
otherwise EXCEPTION_TIER = TIER;
end;
run;
Below is the LOG with Errors:
50 when ('8') EXCEPTION_TIER = %exception(TIER); ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: %substr(&finish, %length(&finish), 1) ERROR: The %FROM value of the %DO I loop is invalid. 50 when ('8') EXCEPTION_TIER = %exception(TIER); _ 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: R+1 ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition. ERROR: The %TO value of the %DO I loop is invalid. ERROR: The macro EXCEPTION will stop executing.
The out I desire:
STUDENT_NO | TEST_SCORE | TIER | EXCEPTION_CD | EXCEPTION_TIER |
1 | 70 | Tier_3 | Tier_3 | |
2 | 80 | Tier_2 | 8 | Tier_3 |
3 | 90 | Tier_1 | 8 | Tier_2 |
4 | 65 | Tier_0 | Tier_0 |
Any solution would be helpful.
Thanks,
RV.
Sorry, I'm having a hard time understanding what you are trying to do. Macro issues aside, does the code I posted give you the output you would want for the sample data?
If so, what parts of the code do you want to make dynamic, just the word Tier?
If so, then perhaps a macro like:
%macro exception(finish);
cats("&finish._",input(scan(&finish,2,'_'),1.)+1)
%mend exception;
data want;
set data_in;
select (EXCEPTION_CD);
when ('8') EXCEPTION_TIER = %exception(Tier);
otherwise EXCEPTION_TIER = TIER;
end;
run;
?
1) there is no example data, we do not have your test.datain data set.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
2) you apparently have a number of macro variables that we have no idea what values they may have. I suppose they are hidden in the %include file.
3) What help are you asking for? What is this code supposed to do? Do you get errors? Post code an log. Since this involves a macro you should probably have the options mprint symbolgen; on before running the macro. Post the log in a code box opened with the forum {i} menu icon to keep the format of any warning or error messages.
My apologies Ballard. Rookie mistake.
I am updating the code with sample data and will load again with Log Information in a couple moments.
From the documentation
call execute('%nrstr(%sales('||month||'))');
I don't think you need a macro in this setting.
The code below uses the SCAN() function to grab the number in TIER after the underscore, uses the input() function to convert it from a character value to a numeric value, adds one to that value, and then uses the CATS function to concatentate that value onto "Tier_".
data want;
set data_in;
select (EXCEPTION_CD);
when ('8') EXCEPTION_TIER = cats("Tier_",input(scan(Tier,2,'_'),1.)+1);
otherwise EXCEPTION_TIER = TIER;
end;
run;
@Quentin Thanks for the response. However, I do need this in a Macro Function. The Value "Tier_" in your CATS, is dynamic and can and will change. NOOB Data: I have multiple Programs in a Project, and the final programs will reference this Macro (Global) to identify if there is an exception, and depending on the either the SELECT; DATA STEP or CASE PROC SQL Statement, create an Exception Tier.
Hope that clarified.
Thanks for the response none the less.
Sorry, I'm having a hard time understanding what you are trying to do. Macro issues aside, does the code I posted give you the output you would want for the sample data?
If so, what parts of the code do you want to make dynamic, just the word Tier?
If so, then perhaps a macro like:
%macro exception(finish);
cats("&finish._",input(scan(&finish,2,'_'),1.)+1)
%mend exception;
data want;
set data_in;
select (EXCEPTION_CD);
when ('8') EXCEPTION_TIER = %exception(Tier);
otherwise EXCEPTION_TIER = TIER;
end;
run;
?
@Quentin Probably marked this as a solution too soon. Still do need your help.
Your solution works, however, what it actually does for
cats("&finish._",input(scan(&finish,2,'_'),1.)+1)
is when then condition is true for
when ('8') EXCEPTION_TIER = %exception(Tier);
It uses the actual String "Tier" in the conditional Statement, instead of what is in the Variable.
For Example, one of the Tiers would be called Test_Tier_0, and with satisfied condition, I should get Test_Tier_1.
Your method provides me with Tier_1.
I can create a new post, but don't want to pollute the forum with this request.
Sorry, I'm losing the big picture.
When you said the value "Tier" was dynamic, I thought you meant you wanted to be able to specify the text value.
Now I think you mean there will always be a variable named Tier, which has a value in it, and the end of value is an underscore followed by a number. And when Exception_CD='8', you want to set Exception_Tier to have the same "base value" as Tier, with the numeric suffix incremented. Is that right?
So is it just that you need code that will compute the below variable WANT from the variable HAVE?
Have Want
Tier_2 Tier_3
Tier_1 Tier_2
Test_Tier_0 Test_Tier_1
Blah_6 Blah_7
If so, then I think the process could be: find the base value of the HAVE variable (i.e. value up to the last underscore), find the value of the the numeric suffix, incrementthe value of the numeric suffix, write the WANT variable by concatenating the base value with the numeric suffix.
But I'm back to thinking this does not need to be a macro, so maybe I'm not understanding.
More details:
1. Is there always an underscore in the value, and always a numeric suffix after the last underscore?
2. Is the numeric suffix always between 0 and 8, or if not, what is the range of possible numeric suffixes?
3. Wouldn't life be easier if you just permanently separated the alpha part of the Tier variable (with values like "Tier", "Test_Tier" etc) into one variable, and the numeric suffix into another variable?
BTW, feel free to un-accept my earlier answer.
@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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.