topic Re: ODBC fields formatting different in SAS Data Management

ODBC fields formatting different

SVoldrich — Mon, 28 Oct 2013 16:15:02 GMT

I have a quirk -

last week I asked how to change a format on a timestamp field from $26. to date and you guys were awesome with that answer...

i moved the program to my remote desktop to let it run over there and not tie up my laptop.

BUT...

when i run the program on my remote desktop it actually sees that field as a date/time and not as the character.

Exact same code (litterally copied and pasted the .sas file from one drive to another)

Exact same tables (ODBC connection)

Exact same program (SAS 9.2)

The only thing that changed was the computer i'm running it on.

Has anyone experienced this?

Why is it doing this?

is there a setting to change on the remote so i don't have to rewrite my code?

Re: ODBC fields formatting different

ballardw — Mon, 28 Oct 2013 18:07:58 GMT

I would suspect that the ODBC driver versions may differ, especially if different versions of the OS.

Re: ODBC fields formatting different

SVoldrich — Mon, 28 Oct 2013 18:15:21 GMT

nope.

exact same driver, exact same OS (Win7)

Re: ODBC fields formatting different

JBailey — Mon, 28 Oct 2013 18:20:30 GMT

Hi,

Can you post the SAS logs from a run on each machine?

Re: ODBC fields formatting different

SVoldrich — Mon, 28 Oct 2013 18:53:45 GMT

the driver totally makes sense... but i've checked it 3 times now...

It won't let me paste - but here is the issue:

When I do proc sql; describe table [table]; quit;

I get what you would expect when I run the program.

Note: SQL table [table] was created like:

create table [table] (

and then it lists the fields as it should.

everything matches except for this field:

On my remote desktop i get this:

ACTY_TMSTP num format=DATETIME25.6 informat=DATETIME25.6 label='ACTY_TMSTP'

On my laptop i get this:

ACTY_TMSTP char(26) format=$26. informat=$26. label='ACTY_TMSTP'

Of course, this is the field I am working with. I wrote code to compare this date with others - so now to run it on the remote desktop i need to change the code...

Re: ODBC fields formatting different

Reeza — Mon, 28 Oct 2013 19:00:07 GMT

Use a browser other than IE to paste or paste into text file then into the editor here.

Re: ODBC fields formatting different

Reeza — Mon, 28 Oct 2013 19:02:21 GMT

Depending on your code you may want to consider Pass - Thru SQL instead which shouldn't have that issue.

Re: ODBC fields formatting different

SVoldrich — Mon, 28 Oct 2013 19:02:38 GMT

Sorry, our office only uses IE.

Re: ODBC fields formatting different

SVoldrich — Mon, 28 Oct 2013 19:04:43 GMT

Isn't that what proc sql is? (pass thru?)

Re: ODBC fields formatting different

JBailey — Mon, 28 Oct 2013 19:09:11 GMT

PROC SQL will allow you to enter SAS SQL code or explicitly pass DBMS-specific code to the database.

What is your target database? Which ODBC driver are you using?

Copy your SAS logs to a text editor (Notepad) then high-light that text and re-copy it. You should be able to paste it into the Communities site.

Re: ODBC fields formatting different

Reeza — Mon, 28 Oct 2013 19:11:24 GMT

No, for example you can run SQL code on SAS datasets that aren't on a SQL server so definitely not Pass-Thru.

Pass Through will submit SQL code to your server, so the DBMS type is important. It can be more efficient because work is then done on the server not on your computer.