Greetings,
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.
Any workarounds?
Your help is very much appreciated.
Thank you very much.
Cheers,
Yeti.
I'm assuming you have a character field...
Have you tried either the cmiss, compress or missing function in SAS?
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.
Thanks.
Yeti
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.
Ksharp
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.
Thanks.
Yeti
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?"
from xxxxx.xxxxxxxxxxxxxx;
quit;
shows:
Hope this helps.
Thanks.
Yeti
One work-around is to use explicit SQL pass-thru.
Thanks LinusH.
That was exactly what I was looking for. I did not even know such a thing existed.
Cheers.
Yeti.
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.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001384390.htm
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.
To control how SAS missing character values are handled by Teradata, use the NULLCHAR= and NULLCHARVAL= data set options.
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.
Cheers,
Yeti.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: