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;
