BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
sivaji
Fluorite | Level 6
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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 4 replies
  • 1412 views
  • 0 likes
  • 3 in conversation