BookmarkSubscribeRSS Feed
krithi
Calcite | Level 5

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 ?

6 REPLIES 6
FriedEgg
SAS Employee

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;

Howles
Quartz | Level 8

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;

Doc_Duke
Rhodochrosite | Level 12

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

puneet_gandhar
Calcite | Level 5

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 

FriedEgg
SAS Employee

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.

Patrick
Opal | Level 21

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1496 views
  • 0 likes
  • 6 in conversation