Proc SQL and SAS Date Variables

[buechler66]

Hi. I have the following Proc SQL that is selecting ri.status_1_dt as istatus1date, which is a Datetime column on the Oracle table. I need for istatus1date to be saved in the SAS dataset as a SAS Date type variable with only the date portion retained, so it should have no timestamp as part of the SAS date.

As it stands now the variable ends up in the SAS dataset as a Char.  Can anyone help me with this?

proc sql;                                      
  connect to oracle as its (USER=&orauser PASSWORD=&orapass path ='fcprod');
  create table ireps as
    select *
  from connection to its (select 
  ri.REPORTER,
  rep.FIRST_NAME ifname,
  trunc(ri.status_1_dt) as istatus1date
  from ttms.reporter_imap ri, ttms.reporter rep
	where ri.reporter=rep.reporter);
disconnect from its;
quit;

[Reeza]

Doesn't trunc return a character variable? I recommend bringing it as a datetime and then processing it on SAS side. 

The SAS function for the conversion would be DATEPART()

[ChrisNZ]

You need to get the date part in SAS as @Reeza said.

Either you repeat the variable list:

proc sql;                                      
  connect to oracle as its (USER=&orauser PASSWORD=&orapass path ='fcprod');
  create table IREPS as
    select  REPORTER
          , IFNAME
          , datepart(STATUS_1_DT) as ISTATUS1DATE
    from connection to its (select 
       ri.REPORTER
      ,rep.FIRST_NAME ifname
      ,ri.status_1_dt
      from ttms.reporter_imap ri, ttms.reporter rep
    	where ri.reporter=rep.reporter);
  disconnect from its;
quit;

or you dont:

proc sql;                                      
  connect to oracle as its (USER=&orauser PASSWORD=&orapass path ='fcprod');
  create table IREPS(drop=STATUS_1_DT) as
    select  *
          , datepart(STATUS_1_DT) as ISTATUS1DATE
    from connection to its (select 
       ri.REPORTER
      ,rep.FIRST_NAME ifname
      ,ri.STATUS_1_DT
      from ttms.reporter_imap ri, ttms.reporter rep
    	where ri.reporter=rep.reporter);
  disconnect from its;
quit;