topic ERROR: Function TRUNC requires at least 2 argument(s). in SAS Programming

ERROR: Function TRUNC requires at least 2 argument(s).

bhca60 — Mon, 19 Feb 2024 19:18:00 GMT

proc sql;
     /*connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); */

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.act_dttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.inptid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm,
         			 c.mktcd,
                                 c.srcstcd,
                                 d.pur_id,
                                 d.bol_cd,
                                 d.clmplcd,
			         d.ent,
                             case when d.plancd in ('MMM','RRR','DDDD') then 'RNM'
                                  else 'INE'
                             end as Business,
                                 d.plancd
          
                    from dra.rsh_dra_snart_act as a

                       left join dra.rsh_dra_case as c
                          on a.id = c.id

                       left join dra.rsh_dra_nonmdm_mbr as d
                          on c.id = d.id 
				
                          where trunc(a.act_dttm) between to_date(&start.,'yyyy-mm-dd') and to_date(&end.,'yyyy-mm-dd')
                            and a.inptid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)
        
                        /*  order by a.id, a.act_dttm*/);
                         
     /*disconnect from test;*/
quit;

ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TO_DATE could not be located.
ERROR: Function TO_DATE could not be located.

Should I stick with using TRUNC or use datepart instead?

Re: ERROR: Function TRUNC requires at least 2 argument(s).

PaigeMiller — Mon, 19 Feb 2024 19:52:09 GMT

I am certainly not knowledgeable about using Oracle commands within PROC SQL ... however a quick Google search finds many answers, such as this one: https://docs.oracle.com/cd/B13789_01/olap.101/b10339/x_trap012.htm#:~:text=The%20TRUNC%20function%20truncates%20either,TRUNC%20(for%20numbers)

Re: ERROR: Function TRUNC requires at least 2 argument(s).

SASKiwi — Mon, 19 Feb 2024 20:00:55 GMT

If you are switching to SAS SQL then you don't need TRUNC and you'll have to remove TO_DATE as there is no such function in SAS. I'm assuming act_dttm is an Oracle datetime here:

 where a.act_dttm between "01Jan2024:00:00:00"dt and "31Jan2024:00:00:00"dt