BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yeti
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

Data never sleeps

View solution in original post

9 REPLIES 9
Reeza
Super User

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

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

Yeti
Calcite | Level 5

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

Ksharp
Super User

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

Yeti
Calcite | Level 5

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

Yeti
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
Yeti
Calcite | Level 5

Thanks LinusH.

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

Cheers.

Yeti.

FriedEgg
SAS Employee

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.

Yeti
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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