topic Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit) in SAS Programming

Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

krithi — Fri, 20 Apr 2012 12:53:12 GMT

We are trying to connect to SQL server using ODBC from SAS 9.3 and getting unexpected results for the same.

Here are a couple of examples -->

SAS Codes Submitted (two examples below)

Results Based upon Server Configuration used –

32 bit server and windows 2003 OS

SQL 2005 standard edition 32 bit

SAS 9.1 32-bit

Results Based upon Server Configuration used –

64 bit server and windows 2003 OS

SQL 2005 Standard edition 64 bit

SAS 9.3 64-bit

Proc sql noprint;

select distinct year(campaign_end_date) into: year from SQL.campaign where campaign_id eq "014100094";

Quit;

%put &year.;

The output value we are getting in macro variable ‘year’ is 2011.

The output value we are getting in macro variable ‘year’ is 1951.

Proc sql noprint;

Select year(campaign_end_date) into: year2 from SQL.campaign where campaign_id eq "014100094";

Quit;

%put &year2.;

The output value we are getting in macro variable ‘year2’ is 2011.

“If we simply remove ‘distinct’ from the above query;“

The output value we are getting in macro variable ‘year2’ is 2011.

Is anyone aware of any nuances of connecting to SQL server using SAS 9.3 (64 bit) with the help of ODBC ?

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

FriedEgg — Fri, 20 Apr 2012 16:02:15 GMT

In your syntax the first rows value will be the value of the macro variable (both in 9.1 and 9.3) there must be something different with the ordering, in some way, observed between the two queries. If you always want to retrieve the most recent year you should rewrite this query anyway to ensure the you get the date you are looking for using either MAX() function or an ORDER BY statement.

proc sql noprint;

select year(min(campaign_end_date)), year(max(campaign_end_date))

into :min_year, max_year

from sql.campaign

where campaign_id eq "014100094";

quit;

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

Howles — Fri, 20 Apr 2012 16:56:22 GMT

If FriedEgg's theory is right, you should see multiple year values if you turn off the NOPRINT option.

FriedEgg wrote:

In your syntax the first rows value will be the value of the macro variable (both in 9.1 and 9.3) there must be something different with the ordering, in some way, observed between the two queries.  If you always want to retrieve the most recent year you should rewrite this query anyway to ensure the you get the date you are looking for using either MAX() function or an ORDER BY statement.

proc sql noprint;
select year(min(campaign_end_date)), year(max(campaign_end_date))
into :min_year, max_year
from sql.campaign
where campaign_id eq "014100094";
quit;

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

Doc_Duke — Fri, 20 Apr 2012 17:33:09 GMT

FriedEgg nailed it. SQL has no inherent ordering (not in SAS nor either version of SQL/Server), so &year is getting the last record that it decided to process that time. That can vary with both the version of SQL and performance options that may have been set. It can even change from run to run on the same data and SQL processor. The only way to force an order is to use the ORDER BY statement.

Doc Muhlbaier

Duke

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

puneet_gandhar — Mon, 23 Apr 2012 04:12:41 GMT

Dear All,

Thanks for your support and guidance. We were suggested "orderby" earlier too, but we missed a point here. We are using the data from a SQL table where there are only 6 observations which are all having the observations as 2011 and none of the observations is having 1951 as a value.

Thanks again for your support.

puneet

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

FriedEgg — Fri, 27 Apr 2012 00:10:01 GMT

Please provide some additional information. Particullary the table structure in SQL Server you are querying here. Are you sure no updates to ODBC drivers were performed or alterations anywhere else beside updating your SAS version.

Re: Proc SQL code behavior difference when moved from SAS 9.1(32 bit) to SAS 9.3 (64 bit)

Patrick — Fri, 27 Apr 2012 01:18:55 GMT

Add options sastrace='d' sastraceloc=saslog nostsuffix; before your SQL code and then let us know how the SQL from the log which was actually sent to the server looks like.

FriedEgg's ODBC drivers question: Good thought! Did you check that you're actually still connecting to the same database like before?