SAS vs MS SQL record count

[Citrine10]

hi there,

i run a query in MS SQL where the query selects all records from 4 tables and unions them. Result set is then 249236 records

I copied the same code in SAS and placed it in the PROC SQL command yet i get 286966 records. This is  a difference of 37736 records and the code is the exact same. Do you know why?

[jimbarbour]

Can you post the code for each including the LIBNAME statements?

[Citrine10]

there is no lib.

SAS code:
proc sql;
select name from Table1
union
select name from Table2
;quit;

SQL code:
select name from Table1
union
select name from Table2

[Reeza]

Then you're reading from your work library not the server so the expectation that the results are the same is incorrect.

[Citrine10]

I dont think i understand as i connect directly to SQL server from SAS

[jimbarbour]

To @Reeza's point, there is always a Lib in SAS.  If it's not coded, then it's WORK by default.

For your SAS code, can you post everything from the PROC SQL to the QUIT?

For your SQL server code, can you post actual code?

Jim

[Citrine10]

I am unable to post the actual code as its confidential. what code be the difference in record count?

[Reeza]

1. Your expectation that they should be the same is incorrect because of an underlying difference in the data timeliness
2. You counted things incorrectly
3. You have deleted observations in the SQL database that are being counted
4. The code isn't the same between the processes, but you think it is.

[Citrine10]

I'm sorry but i do not get what you are saying. Please elaborate 

[Quentin]

There are lots of options for what could be going wrong.

I would recommend you make a small test query which replicates the problem, and is something you would be able to share. 

You might also check the counts you get from just select * from both tables, and make sure those match.

Finally, UNION will (by default) return distinct values.  You might change to UNION ALL and see if they will match.  I don't know MS SQL server that well, I suppose it's possible to set a SQL server database as case-insensitive, which could lead to SQL server treating more values as duplicates than SAS would.  

[SASKiwi]

Simplify your problem by doing row counts on single tables in SQL Server and in SAS then compare results.

Identify which tables show a different row counts - all or just some? 

[Citrine10]

hi thank you for the suggestions. i did a ccomparison and i see that the time is pulling with the date, thus there are multiple records.

How do i get only the date from a datetime SQL column?

[Tom]

---

@Citrine10 wrote:

hi thank you for the suggestions. i did a ccomparison and i see that the time is pulling with the date, thus there are multiple records.

How do i get only the date from a datetime SQL column?

---

I am not sure why MS-SQL would consider two different datetime values as being the same just because they occur on the same date, but in SAS code you can use the DATEPART() function to convert a datetime value (number of seconds) into a date value (number of days).  Make sure to change the format attached since displaying a date value as if it was a datetime value will make all reasonable dates look like they are some time early in the morning of 01JAN1960.

You can also change all the datetime values to the datetime value for the start of the day by using the INTNX() function with the DTDATE interval.

[Ksharp]

It might be due to NULL and Missing value defined in SAS and DB.
In any DB like MS SQL , Null is different from MISSING, but SAS take both as the same value for the sake of compatibility .