11-16-2011 07:18 PM
I apologize in advance if that has been asked here many times, but for the sake of it, I can not find the answer I am looking for.
I have a column/field in a table that has values including 'spaces'. I am trying to find a way to find the records where the column is null.
Can this be done?
When I simply use the where clause in PROC SQL, 'where column is null', this results in wrong number of records.
The source table is actually a Teradata database, in which I can easily distinguish space with null using a different interface, such as SQL assistant.
But I need to write some scripts against this table in SAS.
Your help is very much appreciated.
Thank you very much.
11-16-2011 07:45 PM
Thanks Reeza. I have used compress, missing, = " ", = '', tranwrd(), nothing gave me the expected result. But I have not used cmiss so far. I will give that a shot and let you know.
And yes, it is of character data type.
11-16-2011 10:12 PM
I doubted that your space is not really space. They are some blank character or non-printable character.
Such as TAB character '09'x and hex blank character '00'x.
You should use compress() to remove these character firstly.
11-16-2011 10:34 PM
Thanks Ksharp. Yes, they are spaces. $hex4. value shows they are all '20'. I have also used compress(column,,'kw'), still treats them as null.
I am lost really.
11-16-2011 10:50 PM
The picture below is a screenshot from Teradata SQL Assistant.
As you can see the second column does not have '?' indicating it is not null. Note the 4th column, they are all null.
In SAS, the same table with this query
proc sql ;
select clm_hdr_key, SRC_ITS_PNT_OF_SVC_LVL_CD label "its service point of service level code",
SRC_ITS_PNT_OF_SVC_LVL_CD is null label "is null?",
SRC_LINE_CLM_CHK_BYP_CD label "claim check bypass code line",
SRC_LINE_CLM_CHK_BYP_CD is null label "is null?"
Hope this helps.
11-17-2011 06:22 PM
Whenever using an outside data source you need to consider is data types. Teradata handles nulls differently that SAS so you need to account for this.
Teradata has a special value that is called NULL. A Teradata NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Teradata NULL value, it interprets it as a SAS missing value.
By default, Teradata columns accept NULL values. However, you can define columns so that they do not contain NULL values. For example, when you create a SALES table, define the CUSTOMER column as NOT NULL, telling Teradata not to add a row to the table unless the CUSTOMER column for the row has a value. When creating a Teradata table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.
For more information about how SAS handles null values, see Potential Result Set Differences When Processing Null Data.
11-17-2011 07:32 PM
Thanks FriedEgg. Very kind of you to share the documentation about Teradata in SAS. I have boomarked it.
I am slowly learning about SAS and finally beginning to get comfortable with it.
Need further help from the community? Please ask a new question.