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 ?
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;
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;
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
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
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.