topic Re: Where clause with missing dates in SAS Programming

Where clause with missing dates

Bellefeuille — Fri, 18 Aug 2017 02:31:12 GMT

Good Evenening,

Appreciate all the help in learning 🙂

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;

Re: Where clause with missing dates

RahulG — Fri, 18 Aug 2017 02:51:34 GMT

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.

Re: Where clause with missing dates

Bellefeuille — Fri, 18 Aug 2017 02:55:28 GMT

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.?

Re: Where clause with missing dates

PGStats — Fri, 18 Aug 2017 03:01:36 GMT

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;

Re: Where clause with missing dates

Bellefeuille — Fri, 18 Aug 2017 10:56:48 GMT

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

Re: Where clause with missing dates

RahulG — Fri, 18 Aug 2017 11:14:49 GMT

Try using

WHERE CU.DEATH_DT is not null

Re: Where clause with missing dates

ballardw — Fri, 18 Aug 2017 14:58:14 GMT

@RahulG wrote:

Try using
WHERE CU.DEATH_DT is not null

Where not missing(Cu.Death_DT)