topic Re: Results not displayed in SAS Programming

Results not displayed

Kayson — Thu, 12 Jul 2018 23:33:52 GMT

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;

Re: Results not displayed

SASKiwi — Thu, 12 Jul 2018 23:40:28 GMT

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.

Re: Results not displayed

Shmuel — Fri, 13 Jul 2018 00:49:09 GMT

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 ?

Re: Results not displayed

Kayson — Fri, 13 Jul 2018 12:01:42 GMT

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

Re: Results not displayed

Shmuel — Fri, 13 Jul 2018 12:32:06 GMT

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.