Data Step subsetting throwing errors

[inquistive]

Hi Experts,

I am stuck on a very simple code. Quick help is appreciated. I have code like this:

Data farmer;

set agriculture;

if land_ownership = 'YES' and &today. between  ownership_start_date and ownership_end_date; (Please note '&today.' is predefined and global macro available in library) 

run;

This throws following errors: 388-185: Expecting an arithmetic error.

                             200-322:  the symbol is not recognized and will be ignored.

                            76-322: Syntax error, statement will be ignored.

[PaigeMiller]

When there are errors in the log, please show us (and in the future every time this happens please show us) the entire LOG for this entire section of code, all of it, every single line of the log for this section of code. Please do not show us the errors disconnected from the code. Please format the log properly by copying the log as text and pasting it into the window that appears here at the SAS Communities when you click on the </> icon.

We also need to see the actual value of macro variable &today

[inquistive]

Line generated by the macro variable "TODAY".

[PaigeMiller]

---

@inquistive wrote:
Line generated by the macro variable "TODAY".

---

I don't understand. And this is not the log that I requested.

[inquistive]

Thank you for your support. However, I can't show the log here(I said 'code look LIKE this'). I am just seeking advice on what has gone wrong in the syntax that SAS is objecting to.

[PaigeMiller]

It's really hard to say anything if you can't show us the log, and I assume you can't show us the data either. We can guess, but often the guess is way off.

[Quentin]

Hi,

I'm assuming you can't show the real code / log because it has confidential information of some sort.

In such a case, the best thing to do is create a non-confidential test case  which reproduces the problem (using a small data step with data from the CARDS statement or whatever fake data).  Then you can post that the real code and log from the test case.

In my experience, about 50% of the time when you try to make a small test case to replicate the problem in preparation for posting a question, it in fact does NOT replicate the problem (surprisingly).  And then you realize the problem is not where you thought it was, and you end up solving the problem yourself.

And in the 50% of the cases where you can't solve it yourself, by posting actual sample code and log that shows the error, you double your chances of getting a quick solution from the community.

[FreelanceReinh · Accepted solution]

Hi @inquistive,

In the DATA step, the BETWEEN-AND operator is valid only in WHERE conditions. So, either use a WHERE statement instead of an IF statement (if this is possible in your real code) or use the "<=" operator (or its mnemonic LE).

if land_ownership = 'YES' and ownership_start_date <= &today. <= ownership_end_date;

There are likely more issues in your code because neither the error "200-322" nor the note "Line generated by ..." should occur if macro variable TODAY contained a valid SAS date value such as '29OCT2021'd or 22582.

[inquistive]

Thank you! @FreelanceReinhard.You saved my day!!

[ballardw]

---

@inquistive wrote:
Thank you! @FreelanceReinhard.You saved my day!!

---

Be aware that if you have missing values for a variable they are always less than any actual value.

So use of

if land_ownership = 'YES' and ownership_start_date <= &today. <= ownership_end_date;

would return records where Ownership_start_date is missing. From context you might not think that important but I find stupid things involving dates all the time. Like dates of birth after the date of a medical service, end dates occurring before start dates.

Also if the Ownership_end_date is missing it will not return an observation. Since end dates may be missing depending on context of your data until the actual end occurs this is something to consider.

[Quentin]

---

@ballardw wrote:

---

Be aware that if you have missing values for a variable they are always less than any actual value.

So use of

if land_ownership = 'YES' and ownership_start_date <= &today. <= ownership_end_date;

would return records where Ownership_start_date is missing. From context you might not think that important but I find stupid things involving dates all the time. Like dates of birth after the date of a medical service, end dates occurring before start dates.

Also if the Ownership_end_date is missing it will not return an observation. Since end dates may be missing depending on context of your data until the actual end occurs this is something to consider.

---

Sometimes I do stuff like:

if land_ownership = 'YES' and .Z < ownership_start_date <= &today. <= ownership_end_date;

to make explicit that all the values need to be non-missing.

Or, since I'm a fan of assertions to validate expectations, I add an assertion that will throw an error if ownership_start_date or ownership_end_date are missing (or in wrong order):

%assert(land_ownership IN ("YES" "NO"))
%assert(.Z < ownership_start_date <= ownership_end_date )

With %assert defined like:  https://www.lexjansen.com/nesug/nesug12/cc/cc31.pdf .