topic Re: Macro variables in substr function in SAS Programming

Macro variables in substr function

Babloo — Tue, 10 Dec 2019 16:31:29 GMT

I would like to know how to use substr function with macro variables. I have the macro variables as follows and I would like to look for first five characters and select the last three characters based on the condition as mentioned below. Not sure it is right and I do not have SAS to execute to it (downtime now). Correct me if I'm wrong.

%let ent_curr=10000_EUR; /*i have many macro variables like this*/

/*and I have to write the condition as like below*/
/*given below is sample condition and there are some conditions as similar which I have to write*/

Case 
When entity =substr(&ent_curr.,1,5) then substr(&ent_curr.,7,9)
Else 
Currency_cd
End;

Re: Macro variables in substr function

PaigeMiller — Tue, 10 Dec 2019 17:51:43 GMT

SUBSTR works on a text string

substr(&ent_curr.,1,5)

tells SAS to use the text string value of variable by the name of &ENT_CURR, which resolves to the text string value of the variable named 10000_EUR, but the variable named 10000_EUR doesn't exist.

To make SUBSTR work on a text string and not the value of a variable, you have to enclose &ENT_CURR in ______________________. (Answer to be provided by the user)

Re: Macro variables in substr function

Tom — Tue, 10 Dec 2019 16:43:45 GMT

To help you we need to know the types of the ENTITY and CURRENCY_CD variables.

Assuming the first is numeric and the second is character you want to do this, using the macro function %SUBSTR() instead of the SAS function SUBSTR(). So when ENT_CURR is 10000_EUR then this code:

case when entity=%substr(&ent_curr.,1,5) then "%substr(&ent_curr.,7,9)"
     else Currency_cd
end

Will generate this SAS code:

case when entity=10000 then "EUR"
     else Currency_cd
end

Re: Macro variables in substr function

Reeza — Tue, 10 Dec 2019 16:45:29 GMT

Always do the text replacement to see what would happen and if it's valid code.

Case 
When entity =substr(10000_EUR,1,5) then substr(10000_EUR,7,9)
Else 
Currency_cd
End;

You can see the above would not be valid code.

To make it valid you can add quotes around the macro variable so it's treated as text.

Case 
When entity =substr("10000_EUR",1,5) then substr("10000_EUR",7,9)
Else 
Currency_cd
End;

And then replace the quotes around your macro variable to finish the correction.

Re: Macro variables in substr function

Babloo — Tue, 10 Dec 2019 16:46:49 GMT

I don't understand the second part of your explanation. Could you please
help me with one example which works for my scenario?

Re: Macro variables in substr function

Kurt_Bremser — Tue, 10 Dec 2019 16:56:44 GMT

@Babloo wrote:
I don't understand the second part of your explanation. Could you please
help me with one example which works for my scenario?

I strongly suggest that you start making use of the tool that sits between your ears. The answer to @PaigeMiller's exercise is DEAD SIMPLE.

Especially for someone who's been around here for ~1000 posts and probably seen it tens of times in our answers to previous questions.

Re: Macro variables in substr function

FreelanceReinh — Tue, 10 Dec 2019 20:24:31 GMT

Hi @Babloo,

Just to add: The third argument of the SUBSTR and %SUBSTR functions is the length of the substring to extract, not the position of the last character of the substring (although it is tempting to think so). Hence, it should be 3 in your second function call, not 9 (which would be invalid, although not refused). Or you can simply omit the third argument to extract the rest of the string (from starting position 7).

Actually, in your example I'd rather use SCAN (or %SCAN) because it doesn't make assumptions about lengths of substrings.