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;
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?
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?
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.)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: