topic Re: Macro to look up last 4 digits in a table in SAS Programming

Thank you

nancy_mancilla — Thu, 12 Aug 2021 21:41:59 GMT

Thank you so much!

Re: Macro to look up last 4 digits in a table

Tom — Thu, 12 Aug 2021 17:46:37 GMT

The SAS macro processor is a way to allow you to generate CODE.

So what SAS code do you want to run?

What type of variable is PRODUCT_ID?

If it is a character variable then something like:

where product_id like '%889'

should work.

If it is numeric then you want to do something like:

where mod(product_id,1000) = 889

What output do you want? What is the overall code you are trying to create?

Why did you mention macro? Is there some part of the code that needs to change so that you want to use a macro or perhaps just a macro variable to generate that part of the code? How does it change? What are the types of values that you will need to support? How do the different values change how the SAS code you need to run looks?

Re: Macro to look up last 4 digits in a table

nancy_mancilla — Thu, 12 Aug 2021 20:56:16 GMT

Thanks!

Re: Macro to look up last 4 digits in a table

nancy_mancilla — Thu, 12 Aug 2021 18:28:11 GMT

Value to look up
8885

Value expected
1274885
8841885

Re: Macro to look up last 4 digits in a table

Reeza — Thu, 12 Aug 2021 19:22:54 GMT

Use the REVERSE and WHERE with colon operator?
REVERSE flips your variable around so you're comparing the start not the end. This allows you to use the colon operator which will truncate strings to check if they match. Note that if the string can be shorter this may not work as desired - test it with two digit product_ID for example.

data want;
set have;
where reverse(product_ID) in: ('988', '211');
run;

Re: Macro to look up last 4 digits in a table

Reeza — Thu, 12 Aug 2021 19:24:20 GMT

Is that a typo or is that what you expect to match?

@nancy_mancilla wrote:
Value to look up
8885

Value expected
1274885
8841885

Re: Macro to look up last 4 digits in a table

Tom — Thu, 12 Aug 2021 19:41:55 GMT

I don't understand what your current code is doing. Why are you opening a dataset using macro code?

Why are you generating multiple datasets?

If you want to find observations where the last three characters are in a list of values just use:

where substrn(product_id,length(product_id)-2) in ('889' '123')

Thanks!

nancy_mancilla — Thu, 12 Aug 2021 21:42:30 GMT

thanks!

Re: Macro to look up last 4 digits in a table

Reeza — Thu, 12 Aug 2021 21:16:54 GMT

I assure you using IN with the colon as I've shown does work so you're doing it wrong - or trying to do it in SQL, which you've never stated as a requirement. Did you try my example? Or Tom's? I believe Tom's is better and works for either SQL or DATA STEP but has a similar issue where you need to ensure the variable is at least the specified number of characters.

Thank you!!!

nancy_mancilla — Thu, 12 Aug 2021 21:43:01 GMT

Thank you so much!

Re: Macro to look up last 4 digits in a table

Reeza — Thu, 12 Aug 2021 21:36:20 GMT

Macros are not faster - they're faster for the programmer but typically operate slower as you have more code to generate.
A macro is not a good solution here.