SAS Enterprise Guide doesn't read datetimes after upgrade

[techsassy]

Hello, we recently upgraded from SAS Enterprise Guide 6.1 to 8.3. We have a Microsoft SQL Server which provides a table that has datetimes.

When using EG 6.1, SAS would correctly read the datetimes, which could be confirmed either by looking at the Output Datasets (where the datetime columns had a unique symbol to indicate datetime type) or by running proc contents and looking at the variable types. 

Since upgrading to EG 8.3, SAS no longer correctly reads the datetimes; they all appear as strings, instead. Nothing has changed in our database.

Can someone help?

[SASKiwi]

Talk to your SAS administrator. It is highly unlikely that EG alone has caused this issue. What else has changed? The SAS version / release? The OS your SAS server is running on? The SAS/ACCESS product used to read SQL Server? This could be either SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC.

I've seen this type of behaviour when there has been a change in ODBC driver that SAS's ACCESS product has been using and nothing else has changed.

[jimbarbour]

Are you using a Libname and regular Proc SQL or are you using explicit pass through (Connect to DATABASE as...) style SQL?

If you are using a Libname, you could try coding the DATETIME2=YES option on the Libname.  I don't think that's the issue, but it's worth a try.

I would agree with @SASKiwi that an upgrade to Enterprise Guide is unlikely to have caused the problem.  What release of SAS are you on?  Would you be able to run the below SAS code and post the SAS log afterwards?

Jim

OPTIONS	NOSOURCE;
%PUT	&=Syshostinfolong;
%PUT	&=SysVlong4;
OPTIONS	SOURCE;

[techsassy]

Thank you both for your responses. I'm working with ITS to see if we can resolve the issue.

Jim, here is my log, after running the code you suggested:

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program 3';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGSR TEMP;
18 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
19 STYLE=HTMLBlue
20 NOGTITLE
21 NOGFOOTNOTE
22 GPATH=&sasworklocation
23 ENCODING=UTF8
24 options(rolap="on")
25 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
26
27 OPTIONS NOSOURCE;
SYSHOSTINFOLONG=W32_10PRO WIN 10.0.17763 Workstation
SYSVLONG4=9.04.01M7P08052020
31
32 %LET _CLIENTTASKLABEL=;
33 %LET _CLIENTPROCESSFLOWNAME=;
34 %LET _CLIENTPROJECTPATH=;
35 %LET _CLIENTPROJECTPATHHOST=;
36 %LET _CLIENTPROJECTNAME=;
37 %LET _SASPROGRAMFILE=;
38 %LET _SASPROGRAMFILEHOST=;
39
40 ;*';*";*/;quit;run;
41 ODS _ALL_ CLOSE;
42
43
44 QUIT; RUN;
45

[jimbarbour]

OK, it looks like you're running SAS 9.4 M7 on a 32 bit machine with a Windows 10 Professional OS.

SYSHOSTINFOLONG=W32_10PRO WIN 10.0.17763 Workstation
SYSVLONG4=9.04.01M7P08052020

I'm not seeing anything alarming there right off.

Can you post a sample PROC SQL?  I want the Libname and then everything from PROC SQL to QUIT.  I would also like to see the log file.  If I have your code and your log, I should get a pretty good idea of what's going on.

If you would, please use the "Insert SAS code" icon to post the SAS code and the "Insert Code" icon for the log.

Jim