topic Re: Macro Function using data Variable in Proc SQL or Data Step in SAS Enterprise Guide

Macro Function using data Variable in Proc SQL or Data Step

rahulp — Wed, 16 Aug 2017 22:52:03 GMT

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.

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

ballardw — Wed, 16 Aug 2017 22:30:06 GMT

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-data-AKA-generate/ta-p/258712 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.

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

PaigeMiller — Wed, 16 Aug 2017 22:35:47 GMT

From the documentation

https://communities.sas.com/t5/SAS-Enterprise-Guide/Macro-Function-using-data-Variable-in-Proc-SQL-or-Data-Step/m-p/388649#M25057

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||'))');

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

rahulp — Wed, 16 Aug 2017 22:41:54 GMT

My apologies Ballard. Rookie mistake.

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

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

Quentin — Thu, 17 Aug 2017 02:50:49 GMT

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;

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

rahulp — Thu, 17 Aug 2017 13:37:01 GMT

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

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

Quentin — Thu, 17 Aug 2017 13:47:05 GMT

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;

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

rahulp — Thu, 17 Aug 2017 14:18:16 GMT

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

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

rahulp — Thu, 17 Aug 2017 15:29:24 GMT

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

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

Quentin — Thu, 17 Aug 2017 16:30:59 GMT

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.

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

rahulp — Thu, 17 Aug 2017 18:26:02 GMT

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