CODEWITHSUNDEEP
sqloracle-databasepostgresqlsas
B J
B J

Reputation: 140

What does "variable = ." (variable = dot) mean in Oracle SQL/SAS?

I am converting a SAS script to Python, where the SAS script was interfacing with an Oracle database, and the Python will be interfacing with a PostgreSQL database. In the SAS code, I found this statement:

proc sql noprint;
create table table_name as
  select distinct wtn
    from another_table
   where account = .
;

What does "where account = ." mean in Oracle? Or is it not an Oracle SQL thing, and instead is a SAS thing? Or is it available in all forms of SQL?

Further, if it is SAS and/or Oracle specific, what would I use in PostgreSQL in its place?

Upvotes: 3

Views: 16588

Answers (1)

invoketheshell
invoketheshell

Reputation: 3897

In SAS Missing values for numeric variables (including date variables) appear as a period. SAS treats numeric nulls as equal to “the lowest possible number” (essentially negative infinity) when sorting a numeric field.

SAS datasets will have a period as a value for missing data. When you export the data as CSV using proc export, I believe it will create a null value. To really answer your question you will need to know how the ETL from sasdata set to the current database was done.

I expect your data has been normalized for your database. Because of this I think you should use "IS NULL" in place of a "varname = '.' " . See below:

create table table_name as
select distinct wtn
from another_table
  where account is NULL
;

In addition SAS Missing values for character variables appear as blanks. Missing values are set like this for character: if name="none" then name=' '.

Upvotes: 11

Related Questions