topic Re: SAS vs MS SQL record count in SAS Programming

SAS vs MS SQL record count

Citrine10 — Wed, 29 Jul 2020 18:40:59 GMT

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?

Re: SAS vs MS SQL record count

jimbarbour — Wed, 29 Jul 2020 18:54:04 GMT

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

Re: SAS vs MS SQL record count

Citrine10 — Wed, 29 Jul 2020 18:58:26 GMT

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

Re: SAS vs MS SQL record count

Reeza — Wed, 29 Jul 2020 19:06:35 GMT

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

Re: SAS vs MS SQL record count

Citrine10 — Wed, 29 Jul 2020 19:10:16 GMT

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

Re: SAS vs MS SQL record count

jimbarbour — Wed, 29 Jul 2020 19:11:22 GMT

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

Re: SAS vs MS SQL record count

Citrine10 — Wed, 29 Jul 2020 19:14:58 GMT

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

Re: SAS vs MS SQL record count

Reeza — Wed, 29 Jul 2020 19:24:27 GMT

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.

Re: SAS vs MS SQL record count

Citrine10 — Wed, 29 Jul 2020 19:28:19 GMT

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

Re: SAS vs MS SQL record count

Quentin — Wed, 29 Jul 2020 20:17:04 GMT

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.

Re: SAS vs MS SQL record count

SASKiwi — Wed, 29 Jul 2020 20:20:21 GMT

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?

Re: SAS vs MS SQL record count

Citrine10 — Thu, 30 Jul 2020 08:28:09 GMT

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?

Re: SAS vs MS SQL record count

Ksharp — Thu, 30 Jul 2020 12:08:04 GMT

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 .

Re: SAS vs MS SQL record count

Tom — Thu, 30 Jul 2020 12:50:50 GMT

@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.