Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-17-2011 06:49 AM
Super User
Posts: 5,260

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

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

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 17,912

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

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

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

Contributor
Posts: 32

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

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

Super User
Posts: 9,691

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

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

Contributor
Posts: 32

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

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

Contributor
Posts: 32

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

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.

null.PNG

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:

null2.PNG

Hope this helps.

Thanks.

Yeti

Solution
‎11-17-2011 06:49 AM
Super User
Posts: 5,260

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

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

Data never sleeps
Contributor
Posts: 32

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

Thanks LinusH.

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

Cheers.

Yeti.

Trusted Advisor
Posts: 1,300

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

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.

Contributor
Posts: 32

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

Thanks FriedEgg.  Very kind of you to share the documentation about Teradata in SAS.  I have boomarked it. Smiley Happy

I am slowly learning about SAS and finally beginning to get comfortable with it.

Cheers,

Yeti.

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 4219 views
  • 0 likes
  • 5 in conversation