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;
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.
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 ?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.