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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.