## Macro Function using data Variable in Proc SQL or Data Step

Solved
Occasional Contributor
Posts: 13

# Macro Function using data Variable in Proc SQL or Data Step

[ Edited ]

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

Thanks,

RV.

Accepted Solutions
Solution
‎08-17-2017 10:18 AM
PROC Star
Posts: 1,472

## Re: Macro Function using data Variable in Proc SQL or Data Step

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

?

All Replies
Super User
Posts: 13,942

## Re: Macro Function using data Variable in Proc SQL or Data Step

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.

Occasional Contributor
Posts: 13

## Re: Macro Function using data Variable in Proc SQL or Data Step

My apologies Ballard. Rookie mistake.

I am updating the code with sample data and will load again with Log Information in a couple moments.

Posts: 3,288

## Re: Macro Function using data Variable in Proc SQL or Data Step

[ Edited ]

From the documentation

https://communities.sas.com/t5/SAS-Enterprise-Guide/Macro-Function-using-data-Variable-in-Proc-SQL-o...

Tip
The following example uses the %NRSTR macro quoting function to mask the macro statement. This function will delay the execution of macro statements until after a step boundary.
`call execute('%nrstr(%sales('||month||'))');`

--
Paige Miller
PROC Star
Posts: 1,472

## Re: Macro Function using data Variable in Proc SQL or Data Step

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;

``````
Occasional Contributor
Posts: 13

## Re: Macro Function using data Variable in Proc SQL or Data Step

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

Solution
‎08-17-2017 10:18 AM
PROC Star
Posts: 1,472

## Re: Macro Function using data Variable in Proc SQL or Data Step

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

?

Occasional Contributor
Posts: 13

## Re: Macro Function using data Variable in Proc SQL or Data Step

@Quentin Exactly what I wanted. So pretty much, my Macro was incorrect. What you provided, does what I need the Macro to do.

Much Thanks @Quentin. Marking post as answered.

Occasional Contributor
Posts: 13

## Re: Macro Function using data Variable in Proc SQL or Data Step

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

PROC Star
Posts: 1,472

## Re: Macro Function using data Variable in Proc SQL or Data Step

[ Edited ]

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.

Occasional Contributor
Posts: 13

## Re: Macro Function using data Variable in Proc SQL or Data Step

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

``````%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;``````
☑ This topic is solved.