Issue with using TRUNC in my SQL query within PROC SQL

[Jiunn]

Hi,

When I run this query in the SQL environment, it worked ... but when I embed this into proc sql, I keep getting the error message "Function TRUNC requires a numeric expression as argument 2."  Is there a workaround for this?  Thanks in advance for your help!

proc sql;
create table tst as
SELECT common_id,
item_type,
item_amt,
CASE
WHEN a.item_term < '2'
AND a.item_term <> ' ' THEN a.item_term
WHEN bill.cu_bill_term IS NOT NULL THEN t_bill.strm
WHEN t_due.strm IS NOT NULL THEN t_due.strm
ELSE t_eff.strm
END AS item_term
FROM hcs.ps_item_sf a
left join hcs.ps_cu_bill_line bill
ON bill.emplid = a.common_id
AND bill.cu_bill_term = a.item_term
left join hcs.ps_term_tbl t_bill
ON t_bill.acad_career = 'GRAD'
AND Trunc(bill.start_dt, 'Q') = Trunc(t_bill.term_begin_dt, 'Q')
left join hcs.ps_term_tbl t_due
ON t_due.acad_career = 'GRAD'
AND Trunc(a.due_dt, 'Q') = Trunc(t_due.term_begin_dt, 'Q')
left join hcs.ps_term_tbl t_eff
ON t_eff.acad_career = 'GRAD'
AND Trunc(a.item_effective_dt, 'Q') = Trunc(t_eff.term_begin_dt, 'Q')
;
quit;  

[Reeza]

TRUNC isn't a SAS function, you need to find the analogous SAS function, which I think is INTNX().

[Jiunn]

There is a TRUNC function in SAS and I believe the issue is it is recognizing the SQL TRUNC as the SAS TRUNC ... SAS TRUNC requires the 2nd argument to be numeric but Oracle SQL TRUNC allows different arguments like 'Q', 'DDD', ...

I am not sure how I should use INTX in this context - replace the JOIN?

[Reeza]

They're functions with the same name, but I don't believe they do the same functionality.

TRUNC() in SQL appears to round the date to a specific format/interval, whereas TRUNC in SAS truncates a number (no rounding). 

Can you explain what TRUNC with Q and DDD should return? 

---

@Jiunn wrote:

There is a TRUNC function in SAS and I believe the issue is it is recognizing the SQL TRUNC as the SAS TRUNC ... SAS TRUNC requires the 2nd argument to be numeric but Oracle SQL TRUNC allows different arguments like 'Q', 'DDD', ...

I am not sure how I should use INTX in this context - replace the JOIN?

---

[Tom]

There is no SQL function named TRUNC().  There is one that Oracle has created that you can use in SQL code to run in an Oracle database.  Try using INTNX() function instead.

intnx('dtqtr',t_eff.term_begin_dt,0)

Example:

1677  data test;
1678   now=datetime();
1679   want=intnx('dtqtr',now,0) ;
1680   format _all_ datetime19.;
1681   put (_all_) (=);
1682  run;

now=21FEB2019:12:40:35 want=01JAN2019:00:00:00

Or just use PUT() with appropriate format.

put(a.item_effective_dt,dtyyqc6.) = put(t_eff.term_begin_dt, dtyyqc6.)