topic Re: Proc SQL and SAS Date Variables in SAS Programming

Proc SQL and SAS Date Variables

buechler66 — Wed, 26 Apr 2017 21:44:52 GMT

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;

Re: Proc SQL and SAS Date Variables

Reeza — Wed, 26 Apr 2017 22:52:52 GMT

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()

Re: Proc SQL and SAS Date Variables

ChrisNZ — Wed, 26 Apr 2017 23:34:57 GMT

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;