DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 3

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

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 ?

Trusted Advisor
Posts: 1,301

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

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;

Regular Contributor
Posts: 184

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

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;

Trusted Advisor
Posts: 2,116

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

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

N/A
Posts: 1

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

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 

Trusted Advisor
Posts: 1,301

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

Posted in reply to puneet_gandhar

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.

Respected Advisor
Posts: 4,173

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

Posted in reply to puneet_gandhar

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?

Ask a Question
Discussion stats
  • 6 replies
  • 871 views
  • 0 likes
  • 6 in conversation