BookmarkSubscribeRSS Feed
Bellefeuille
Obsidian | Level 7

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;

 

6 REPLIES 6
RahulG
Barite | Level 11

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. 

Bellefeuille
Obsidian | Level 7

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

PGStats
Opal | Level 21

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
Bellefeuille
Obsidian | Level 7

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

 

RahulG
Barite | Level 11

Try using

 

WHERE CU.DEATH_DT is not null
ballardw
Super User

@RahulG wrote:

Try using

 

WHERE CU.DEATH_DT is not null

or

Where not missing(Cu.Death_DT)

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 3652 views
  • 3 likes
  • 4 in conversation