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;
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.
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.?
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;
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
Try using
WHERE CU.DEATH_DT is not null
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.