SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Is proc sql consider a space value NULL?

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Is proc sql consider a space value NULL?

Hi,

I am wondering if in sas proc sql, the space value (" ") is considered as a NULL value?

Thanks,

Eric


Accepted Solutions
Solution
‎08-12-2012 10:41 AM
Regular Contributor
Posts: 184

Re: Is proc sql consider a space value NULL?

If the evaluation is performed by the native SAS implementation of SQL, a space is considered to be null/missing, and can be compared with other values. The host OS (Windows or Unix or whatever) does not matter.

If the evaluation is done by a third-party DBMS (Oracle, MS Access, etc.),it's usually different, because comparisons involving nulls yield nulls. It should not matter whether the DBMS receives the query via pass-thru or libname engine.

Nehcour0420 wrote:

Hi,

I am wondering if in sas proc sql, the space value (" ") is considered as a NULL value?

Thanks,

Eric

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: Is proc sql consider a space value NULL?

Test it on your own system.  I think, at least on Windows, that it is unless you are using a pass thru in which case it would be cross-system dependent.

Solution
‎08-12-2012 10:41 AM
Regular Contributor
Posts: 184

Re: Is proc sql consider a space value NULL?

If the evaluation is performed by the native SAS implementation of SQL, a space is considered to be null/missing, and can be compared with other values. The host OS (Windows or Unix or whatever) does not matter.

If the evaluation is done by a third-party DBMS (Oracle, MS Access, etc.),it's usually different, because comparisons involving nulls yield nulls. It should not matter whether the DBMS receives the query via pass-thru or libname engine.

Nehcour0420 wrote:

Hi,

I am wondering if in sas proc sql, the space value (" ") is considered as a NULL value?

Thanks,

Eric

Respected Advisor
Posts: 4,640

Re: Is proc sql consider a space value NULL?

Windows Office Access (2007) uses a different convention when dealing with Empty and Null strings. In Access, TRUE is represented by -1.:

Capture.PNG

the functions isEmpty and isMissing, although accepted in SQL expressions, are actually VBA functions that do not return anything useful in SQL.

PG

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1879 views
  • 0 likes
  • 4 in conversation