Results not displayed

[Kayson]

Hello all, I'm trying to run the following script in SAS to pull some data from hadoop, it just gives me an empty table with no error message. 

Does anyone know what the issue is? 

proc sql;

create table Cust.winfo as

select w.evn_dt,

w.evn_nm,

w.evn_def,

w.evn_t,

sum(w.ses) as session,

sum(w.cses) as csession,

sum(p.call_ent) as agec,

sum(p.call_ret) as retc,

sum(p.call_b) as call,

sum(p.call_t) as tcall

from

(select a.ses,

a.evn_nm,

a.evn_nb,

cast(to_date(a.evn_ts) as date) as evn_dt,

d.evn_def,

d.evn_t,

count(distinct a.ses) as ses,

count(a.ses) as cses

from db.evn a

inner join db.el d

on a.ses=d.ses

and d.evn_nb=a.evn_nb

WHERE d.evn_def='error'

and to_date(a.evn_ts) >='2018-05-01'

and to_date(a.evn_ts) <='2018-05-31'

GROUP BY a.ses, a.evn_nm, a.evn_nb, to_date(a.evn_ts), d.evn_def, d.evn_t) w

LEFT JOIN

(select 

c.ses,

c.evn_nb,

count(case WHEN c.evn_nm='agent' then 1 END) as agec, 

count(case WHEN c.evn_nm='ret' then 1 END) as retc,

count(case WHEN c.evn_nm='ent then 1 END) as call,

count(case WHEN c.evn_nm='tel' then 1 END) as tcall

FROM db.evn c

WEHRE to_date(c.evn_ts) >='2018-05-01'

AND to_date(c.evn_ts) <='2018-05-31'

AND c.evn_nm IN('ageent', 'ret', 'ent', 'tel')

AND c.ses IN

(select aa.ses

FROM db.evn_el aa

WHERE aa.evn_def='error'

GROUP BY aa.ses)

GROUP BY c.ses, c.evn_nb) p

ON p.ses=w.ses

WHERE(p.evn_nb > w.evn_nb or p.evn_nb is null)

GROUP BY w.evn_dt, w.evn_nm, w.evn_def, w.evn_t;

[SASKiwi]

Your join criteria resulting in 0 rows means there is a problem with the join conditions. We have no way of telling you what the problem is unless we also had access to the data.

If I get a problem like this, I would progressively remove join conditions until rows start to appear to identify the incorrect join. 

[Shmuel]

Your code 

 to_date(a.evn_ts) >='2018-05-01'

and to_date(a.evn_ts) <='2018-05-31'

are you using the right syntax for date literal ?

does function to_date create a char type variable ?

[Kayson]

Yes, it works. I’m not getting any error messages for that part

[Shmuel]

There are 4 select statements in your code.

Check each of them separately, creating datasets (tmp1-tmp4) - does any of then result into 0 obs.

Then combine them by join, one by one.