DATA Step, Macro, Functions and more

Where clause with missing dates

Reply
Contributor
Posts: 21

Where clause with missing dates

Good Evenening,

Appreciate all the help in learning Smiley Happy

 

Love sas!

 

 

Im trying to create a mail list, and in order for myself to do this, i need search for NOT null values in the death_Dt field,

 

I tried NE. and <>"."

 

but its not working

 

%SASTABLE(ONE.CUST,

SELECT CU.CUST_ID

,CU.death_dt

, MAFOCU.FIRST_NA

, MAFOCU.LAST_NA

, MAFOCU.CUST_TYPE_MN

, MAFOCU.FULL_BUSNES_NA1

, MAFOCU.CONTRY_MN

, MAFOCU.MAIL_ROUTE_CD

, MAFOCU.FORMAT_ATTN_LINE_TX

, MAFOCU.FORMAT_SPECL_ADDR_TX

, MAFOCU.FORMAT_STREET_ADDR_TX

, MAFOCU.FORMAT_MNCPTY_ADDR_TX

FROM EDW.CUST CU

JOIN EDW.MAIL_FORMAT_CURRNT MAFOCU

ON MAFOCU.CUST_ID = CU.CUST_ID

WHERE (CU.DEATH_DT <>.));

endrsubmit;

 

Super Contributor
Posts: 271

Re: Where clause with missing dates

Posted in reply to Bellefeuille

I could not understand how SQL query being used in SASTABLE  macro but if I only focus on the SQL query it looks okay to me. 

You are doing inner join and also applied where filter condition. It seems okay to me. You can check whether DEATH_DT column is numeric in the source table. 

Contributor
Posts: 21

Re: Where clause with missing dates

the joins work, and if i search for a specific CUSt_ID,  i get obs, but i only want obs if death_DT is empty, and it seems that null values in the date field are represented as a period.?

Esteemed Advisor
Posts: 5,611

Re: Where clause with missing dates

Posted in reply to Bellefeuille

Try

 

%SASTABLE(ONE.CUST,
SELECT 
  CU.CUST_ID
, CU.death_dt
, MAFOCU.FIRST_NA
, MAFOCU.LAST_NA
, MAFOCU.CUST_TYPE_MN
, MAFOCU.FULL_BUSNES_NA1
, MAFOCU.CONTRY_MN
, MAFOCU.MAIL_ROUTE_CD
, MAFOCU.FORMAT_ATTN_LINE_TX
, MAFOCU.FORMAT_SPECL_ADDR_TX
, MAFOCU.FORMAT_STREET_ADDR_TX
, MAFOCU.FORMAT_MNCPTY_ADDR_TX
FROM 
  EDW.CUST as CU inner JOIN 
  EDW.MAIL_FORMAT_CURRNT as MAFOCU
ON MAFOCU.CUST_ID = CU.CUST_ID
WHERE CU.DEATH_DT is not missing);
endrsubmit;
PG
Contributor
Posts: 21

Re: Where clause with missing dates

when i try this, i get the following

 

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token

"missing" was found following "E CU.DEATH_DT is not". Expected tokens may include:

"<search_condition>". SQLSTATE=42601

 

Super Contributor
Posts: 271

Re: Where clause with missing dates

Posted in reply to Bellefeuille

Try using

 

WHERE CU.DEATH_DT is not null
Super User
Posts: 13,868

Re: Where clause with missing dates


RahulG wrote:

Try using

 

WHERE CU.DEATH_DT is not null

or

Where not missing(Cu.Death_DT)

Ask a Question
Discussion stats
  • 6 replies
  • 194 views
  • 3 likes
  • 4 in conversation