SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

ODBC fields formatting different

Reply
Contributor
Posts: 45

ODBC fields formatting different

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?

Super User
Posts: 11,343

Re: ODBC fields formatting different

Posted in reply to SVoldrich

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

Contributor
Posts: 45

Re: ODBC fields formatting different

nope.

exact same driver, exact same OS (Win7)

Contributor
Posts: 45

Re: ODBC fields formatting different

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...

Super User
Posts: 19,789

Re: ODBC fields formatting different

Posted in reply to SVoldrich

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

Contributor
Posts: 45

Re: ODBC fields formatting different

Sorry, our office only uses IE.

SAS Employee
Posts: 215

Re: ODBC fields formatting different

Posted in reply to SVoldrich

Hi,

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

Super User
Posts: 19,789

Re: ODBC fields formatting different

Posted in reply to SVoldrich

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

Contributor
Posts: 45

Re: ODBC fields formatting different

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

SAS Employee
Posts: 215

Re: ODBC fields formatting different

Posted in reply to SVoldrich

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.

Super User
Posts: 19,789

Re: ODBC fields formatting different

Posted in reply to SVoldrich

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.

Ask a Question
Discussion stats
  • 10 replies
  • 436 views
  • 0 likes
  • 4 in conversation