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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.