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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 1247 views
  • 0 likes
  • 3 in conversation