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?
create table Cust.winfo as
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
cast(to_date(a.evn_ts) as date) as evn_dt,
count(distinct a.ses) as ses,
count(a.ses) as cses
from db.evn a
inner join db.el d
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
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
FROM db.evn_el aa
GROUP BY aa.ses)
GROUP BY c.ses, c.evn_nb) p
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;
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.
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 ?
Friday - last edited Friday
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.