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;
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()
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.