topic How to check for a field that has a 'space' as a valid value? in SAS Procedures

How to check for a field that has a 'space' as a valid value?

Yeti — Thu, 17 Nov 2011 00:18:04 GMT

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.

How to check for a field that has a 'space' as a valid value?

Reeza — Thu, 17 Nov 2011 00:36:24 GMT

I'm assuming you have a character field...

Have you tried either the cmiss, compress or missing function in SAS?

Re: How to check for a field that has a 'space' as a valid value?

Yeti — Thu, 17 Nov 2011 00:45:52 GMT

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

Re: How to check for a field that has a 'space' as a valid value?

Ksharp — Thu, 17 Nov 2011 03:12:11 GMT

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

Re: How to check for a field that has a 'space' as a valid value?

Yeti — Thu, 17 Nov 2011 03:34:23 GMT

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

Re: How to check for a field that has a 'space' as a valid value?

Yeti — Thu, 17 Nov 2011 03:50:47 GMT

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

How to check for a field that has a 'space' as a valid value?

LinusH — Thu, 17 Nov 2011 11:49:23 GMT

One work-around is to use explicit SQL pass-thru.

Re: How to check for a field that has a 'space' as a valid value?

Yeti — Thu, 17 Nov 2011 22:27:33 GMT

Thanks LinusH.

That was exactly what I was looking for. I did not even know such a thing existed.

Cheers.

Yeti.

How to check for a field that has a 'space' as a valid value?

FriedEgg — Thu, 17 Nov 2011 23:22:07 GMT

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.

Re: How to check for a field that has a 'space' as a valid value?

Yeti — Fri, 18 Nov 2011 00:32:44 GMT

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.