BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rahulp
Obsidian | Level 7

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_NOTEST_SCORETIEREXCEPTION_CDEXCEPTION_TIER
170Tier_3 Tier_3
280Tier_28Tier_3
390Tier_18Tier_2
465Tier_0 Tier_0

 

 Any solution would be helpful.

 

Thanks,

 

RV.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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;

 

?

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

10 REPLIES 10
ballardw
Super User

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.

rahulp
Obsidian | Level 7

My apologies Ballard. Rookie mistake.

 

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

PaigeMiller
Diamond | Level 26

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
Quentin
Super User

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;

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
rahulp
Obsidian | Level 7

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

Quentin
Super User

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;

 

?

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
rahulp
Obsidian | Level 7

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

rahulp
Obsidian | Level 7

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

Quentin
Super User

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.

 

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
rahulp
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2304 views
  • 6 likes
  • 4 in conversation