I'm hoping someone here knows what's going on...   I just upgraded to SAS 9.4.  I have one data step and two proc SQL procs that I think should be returning the same result set from SQL Server: libname diablo odbc DATABASE=DEV_Diablo schema=tTrans; proc sql; create table test as select t.* FROM Diablo.Trans t where t.CntyCd = '51059' ; quit; proc sql; connect to odbc (dsn="DEV_Diablo"); create table test1 as select * from connection to odbc ( SELECT * FROM [Diablo].[tTrans].[Trans] where CntyCd = '51059' ) ; disconnect from odbc; quit; data test2; set diablo.Trans; where CntyCd = '51059'; run; [Diablo].[tTrans].[Trans] is a honking big table, and CntyCd 51059 is my home county of Fairfax in Virginia - it's pretty populous.  I would expect at least one million rows to return (population = 1.1 million).  But here's what I get:   1 libname diablo odbc DATABASE=DEV_Diablo 2 schema=tTrans; NOTE: Libref DIABLO was successfully assigned as follows: Engine: ODBC Physical Name: DEV_Diablo 3 proc sql; 4 5 create table test as 6 7 select t.* 8 9 FROM Diablo.Trans t 10 11 where t.CntyCd = '51059' 12 ; NOTE: Table WORK.TEST created, with 415 rows and 395 columns. 13 14 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.62 seconds cpu time 0.21 seconds 29 proc sql; 30 31 connect to odbc (dsn="DEV_Diablo"); 32 33 create table test1 as 34 select * from connection to odbc 35 ( 36 SELECT * 37 FROM [Diablo].[tTrans].[Trans] 38 where CntyCd = '51059' 39 ) 40 ; NOTE: Table WORK.TEST1 created, with 3420645 rows and 395 columns. 41 disconnect from odbc; 42 quit; NOTE: PROCEDURE SQL used (Total process time): real time 19:44.13 cpu time 4:55.51 49 data test2; 50 set diablo.Trans; 51 52 where CntyCd = '51059'; 53 54 run; NOTE: There were 415 observations read from the data set DIABLO.Trans. WHERE CntyCd='51059'; NOTE: The data set WORK.TEST2 has 415 observations and 395 variables. NOTE: DATA statement used (Total process time): real time 0.40 seconds cpu time 0.04 seconds The results from the pass-through SQL are what I would expect from the other two methods, as well.  I'll be using pass-through on this project, but the fact that the other two methods don't return the same result set is making me nervous.  Is this a bug?   ... View more

Hi, All! I am trying to build a sql query that will tell me what percent of a column in a sql server table is populated - i.e. non-missing count / total count. When I run this sql in Microsoft SQL Server Management Studio: select sum(case                       when DocumentationType <> ' '                       then 1                       else 0                   end) as num_pop           ,count(*)  as count           ,sum(case                       when DocumentationType <> ' '                       then 1.00                       else 0.00                     end) / count(*) as pct_pop from raw_eve297_lps I get these results: num_pop   count          pct_pop 135392      525079      0.257850 When I run the same sql in a PROC SQL in SAS, I get these results: The SAS System num_pop     count     pct_pop 135392     525079     0 I KNOW it's something silly I'm overlooking.  Fresh sets of eyes are welcome.  Thanks! ... View more