SAS TIMESTAMP

deleted_user — Thu, 25 Feb 2010 18:08:35 GMT

Hi Iam trying To load data from SAS datasets into a schema in OC for
processing with eLoader. But time is showing as 'TIMESTAMP'.
Here not showing time, just showing a word as TIMESTAMP.
Could you please resolve my code:-

%let userid=%sysget(USERID);
%let passwd=%sysget(PASSWD);
%let dbpath=OCGVPRD1;
%let sasdir=%sysget(FNAME);
%let exclude=%sysget(VIEW);

%put exclude=&exclude;

libname drop oracle user= &userid
password= &passwd
path = "&dbpath" ;
proc datasets lib=drop
kill;
quit;
run;

libname cdwdir oracle user= "&userid"
password= "&passwd"
path = "&dbpath" ;

libname source "&SASDIR";

%macro search(path);
%local filrf rc did memct filename file i path;

%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,&path));
%let did=%sysfunc(dopen(&filrf));
%let memct=%sysfunc(dnum(&did));
%do i=1 %to &memct;
%let filename=%sysfunc(dread(&did,&i));
%let file=%scan(&filename,1,.);
%if %index(%upcase(&filename),.SAS7BDAT) %then %do;

proc sql;
/* drop table cdwdir.&file; */

create table cdwdir.&file as
select * from source.&file;

quit;

proc sql;
connect to oracle (user= &userid
password= &passwd
path = "&dbpath");
execute (grant select on &file
to e_usr) by oracle;
disconnect from oracle;
quit;

%end;
%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend;

************************************************************************************

Get variables for redirecting log file, remove any trailing blanks from variables
used to contruct the name of the log file.

***********************************************************************************;

data _null_;
dt=today();
chardt=put(dt,date9.);
dir = "&SASDIR";
study = "&USERID";
call symput('TODAYDT',compress(chardt,':'));
call symput('DIR',trim(DIR));
call symput('STUDY',trim(STUDY));
run;

filename logfile "&DIR.&STUDY._load_&TODAYDT..log";

proc printto log=logfile new;
run;

%search(&SASDIR);

************************************************************************************

Section setting to uppercase all character fields in the tables loaded.

1) Create temp table with table name and column name for character variables

2) Use this in a loop with update table_name
set column_name = upper(column_name);

***********************************************************************************;

proc sql;

drop table var_cols;

create table var_cols as
select
a.table_name as tabname
, a.column_name as colname
, a.data_type
from
cdwdir.user_tab_columns a
where
data_type like '%CHAR%'
&exclude
;

quit;

proc sql;

drop table upc;
create table upc as
select 'update cdwdir.'||trim(tabname)||' set '||trim(colname)||' = upcase('||trim(colname)||');' as

statement
from
var_cols;

quit;

data _null_;
set upc end=eof;
call symput(trim('ref'||left(_n_)),statement);
if eof then call symput('total',_n_);
run;

%macro runit;
%local i;
%do i=1 %to &total;
proc sql;
&&ref&i;
quit;
%end;
%mend runit;

%runit;

proc printto log=log;
run;

************************************************************************************

Clean up temp datasets (temp, upc & var_cols) as appropriate.

***********************************************************************************;

proc datasets library = work;
delete temp upc var_cols;
run;
quit;

Re: SAS TIMESTAMP

sbb — Thu, 25 Feb 2010 18:29:15 GMT

What's unclear from the OP submission - "But time is showing as 'TIMESTAMP'."

What time variable is showing up as TIMESTAMP?

It would be useful to see SAS log with generated diagnostics and/or resolved macro code and variables which demonstrates the specific problem symptom encountered.

Scott Barry
SBBWorks, Inc.

Re: SAS TIMESTAMP

deleted_user — Thu, 25 Feb 2010 18:57:35 GMT

Thanks Scott,
No isue from sas side, when i sending to oracle clinical then showing the word as TIMESTAMP instead of dates for all date filelds.
Thanks,
Pr

Re: SAS TIMESTAMP

sbb — Fri, 26 Feb 2010 15:33:16 GMT

Help explain what you mean by: "...showing the word as TIMESTAMP...".

I suspect you will want to share your "generated" SAS code in a log with all available diagnostics - post another reply here with the info pasted.

Some key SAS code to help reveal what's useful:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MPRINT;

Scott Barry
SBBWorks, Inc.

Re: SAS TIMESTAMP

sivaji — Tue, 16 Nov 2010 17:27:36 GMT

try with the option "sasdateformat" in connection statement. Check in page no 5.

http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_oracle_7367.pdf

topic SAS TIMESTAMP in SAS Health and Life Sciences

SAS TIMESTAMP

Re: SAS TIMESTAMP

Re: SAS TIMESTAMP

Re: SAS TIMESTAMP

Re: SAS TIMESTAMP