topic Re: Need help with a Macro formula in SAS Programming

Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 19:24:34 GMT

Hello all. I am trying to create this Macro

%LET CODE = svc.BILL_SVC_UNIT_1_CD >= 'L0220' and svc.BILL_SVC_UNIT_1_CD <='L4631' and svc.BILL_SVC_UNIT_1_CD = '97760';

I have tried putting parenthesis around it in various ways but it still doesnt work. It will work if I took off the 97760 portion but I need that. The only other option is to include each and every L code which as one can see is a considerable amount.

Any assistance would be greatly appreciated.

Thanks

Re: Need help with a Macro formula

Reeza — Wed, 24 Jan 2018 19:26:25 GMT

What are you expecting to come out of that formula?

svc.BILL_SVC_UNIT_1_CD is a variable in a data set, but the macro processor won't have access.

I suspect you may need a different approach to whatever problem you're trying to solve.

Re: Need help with a Macro formula

Astounding — Wed, 24 Jan 2018 19:39:11 GMT

This code is 100% fine. It does exactly what it is supposed to do. It assigns a long value to a macro variable named CODE.

Why do you say it is not working? Show the log.

Re: Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 19:48:42 GMT

Thanks Astounding. I was under the same impression. As I stated, it works as long as I do not use the last part. Its when I include it I get no results. The log has no errors it just doesn't produce any lines. If I take off the last part (with the 97760) I get a few thousand lines.

Re: Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 19:51:45 GMT

I use this macro coding everyday as part of my job, its the combination that is not working. If I were to exclude the => <= portion and just run the 97760, again I get results. Perhaps I should use "OR" instead of "AND"?

Re: Need help with a Macro formula

Reeza — Wed, 24 Jan 2018 19:52:57 GMT

Well that last condition is probably mutually exclusive from the first two so it doesn't include any data because nothing meets both conditions. There's nothing wrong with the syntax but the logic is flawed.

Did you mean to have an OR instead of AND?

svc.BILL_SVC_UNIT_1_CD >= 'L0220' and svc.BILL_SVC_UNIT_1_CD<='L4631' 

and svc.BILL_SVC_UNIT_1_CD = '97760';

@wheddingsjr wrote:

Thanks Astounding. I was under the same impression. As I stated, it works as long as I do not use the last part. Its when I include it I get no results. The log has no errors it just doesn't produce any lines. If I take off the last part (with the 97760) I get a few thousand lines.

Re: Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 19:54:00 GMT

I think I got it. It should be OR instead of AND..its now running. Thanks Astounding and Reeza

Re: Need help with a Macro formula

Reeza — Wed, 24 Jan 2018 19:56:07 GMT

Make sure to include parenthesis to properly have the AND and OR evaluate correctly.

I'm assuming this is likely what you want:

(svc.BILL_SVC_UNIT_1_CD >= 'L0220' and svc.BILL_SVC_UNIT_1_CD <='L4631') OR svc.BILL_SVC_UNIT_1_CD = '97760';

Re: Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 19:57:05 GMT

Reeza...looks like we came to the same conclussion at the same time. As I was thinking about the OR instead of AND I see you asked the same question...lol

Re: Need help with a Macro formula

ballardw — Wed, 24 Jan 2018 20:07:42 GMT

You want to be very careful when using < or > comparisons with string values. If you think "L5" < "L4631" you might be somewhat surprised at results. Also for consideration are comparisons where capitalization of characters gets involved. Lower case letters are "greater than" their uppercase equivalents.

Re: Need help with a Macro formula

wheddingsjr — Wed, 24 Jan 2018 20:25:08 GMT

Reeza, I had already run it without the parenthesis but to be on the safe side and to compare I ran it WITH the parenthesis and got 5 more lines than when not using the parenthesis. Going to have to reconcile the two. Thanks for your help!!!