Incorrect Data type being created

[laxmanpai]

Hi ,

i am getting my data from oracle table wherein i am formatting my timestamp columns as follows

proc format;

picture mdyhms low-high ='%Y-%0m-%0d %0H:%0M:%0S.000000' (datatype=datetime);

i am using this format in my code as below 

data Test;

set map;

format eff_datetime mdyhms.;

run;

I am creating a template table using the above Test table.

%macro a (dsn);

%if sysfunc(exist(&sharedlib..&dsn.))  %then %do;

proc sql;

delete from &sharedlib..&dsn.;

quit;

%end;

%else %do;

proc sql;

create table &sharedlib..&dsn. like work.&dsn.;

quit;

%end;

%mend a;

%a (Test);

Now what is happening here is , the template table being created out of the work.test table is having the column eff_datetime with datatype as FLOAT instead of timestamp in Snowflake.

Could someone help me on this. 

[LinusH]

I don't know if this is related, but when we started off testing SAS-Snowflake connection, we ran into problems wtih timestamp/datetime columns.

This since Snowflake by defualt has enabled time zone information.

https://docs.snowflake.com/en/sql-reference/data-types-datetime.html

It can be adjutsed by using a session variable

https://docs.snowflake.com/en/sql-reference/parameters.html#timestamp-type-mapping

which is our work around for us as to disable this. Perhaps there are better ways now (we used ACCESS to ODBC since ACCESS to Snowflake wasn't availble at the time).

[Tom]

So you are asking SAS to define the table in the remote database?

Does it work if you use a normal datetime format for the variable?  

For example like  this:

data &sharedlib..&dsn. ;
  set work.&dsn.;
  format eff_datetime datetime.;
  stop;
run;

If so then make your macro smart enough to do the same thing.

[Sajid01]

Hello @laxmanpai 
I see the following statements in your question/posting
Help me understand your requirement.

1.I am getting my data from oracle table wherein i am formatting my timestamp columns as follows

(You give your format statement)
2.Now what is happening here is , the template table being created out of the work.test table is having the column eff_datetime with datatype as FLOAT instead of timestamp in Snowflake.
So what I understand is that You are getting a date value from oracle and want to convert to a format for snowflake.
Please confirm if my understanding correct?
Please provide sample of date values you are getting from oracle.