topic Re: Can SAS give different output when we have duplicates in join in SAS Programming

Can SAS give different output when we have duplicates in join

Jagadeesh2907 — Tue, 21 Sep 2021 13:38:30 GMT

Hi All,

I have 2 datasets A and B with a key variable "Customer_id".

Dataset A does not have duplicates, however, Dataset B have duplicate key value but the complete record is not duplicate. consider this as a transaction dataset with mulitple amount for each "customer_id".

when i try to do left join of dataset A with Dataset B, in the result dataset C, I would be getting multiple records for "customer_id" which has duplicate values. This is clear.

What is my problem statement is that, will the order of the duplicate customer_id i.e the records of the Customer_id with duplicates differ when we run it each time ?

As far as i know, SAS reads the data record by record and hence this should not change, however, wanted to have an expert opinion or check if my understanding is not correct.

Re: Can SAS give different output when we have duplicates in join

ballardw — Tue, 21 Sep 2021 14:28:15 GMT

Unless you go to some length to force things do not expect SQL to process anything in a given order. If sequence of operation is important then a data step merge may be a better approach. Or if the data is truly a transaction and the desired result is one output record per match then a data step UPDATE may be more in order.

Re: Can SAS give different output when we have duplicates in join

Reeza — Tue, 21 Sep 2021 16:24:51 GMT

SQL doesn't respect order of rows at all. You cannot assume it will generate the same order unless you explicitly control it.
Data steps will by design to some degree but it also assumes that the data being provided is always in the exact same order.

Re: Can SAS give different output when we have duplicates in join

Jagadeesh2907 — Wed, 22 Sep 2021 05:31:33 GMT

this explains my difference. thank you for this point. how can i force SQL to follow a certain order ? is it by sorting the datasets which are needed to be joined ?

Re: Can SAS give different output when we have duplicates in join

Jagadeesh2907 — Wed, 22 Sep 2021 05:32:35 GMT

Thank you Rezza, how can i explicitly control this through SQL ? is it by sorting the datasets before join or there are any other ways ?

Re: Can SAS give different output when we have duplicates in join

Kurt_Bremser — Wed, 22 Sep 2021 05:39:54 GMT

You need a variable (or several variables) with which you can force the order in the ORDER BY clause of PROC SQL.

Note that this will be less performant than establishing the order first with PROC SORT and then join in a DATA step.

Re: Can SAS give different output when we have duplicates in join

ChrisNZ — Wed, 22 Sep 2021 05:50:48 GMT

SQL never guarantees a processing order. That's a "feature" of the language.

You should generate a sequential read, if reading from a SAS V9 dataset, if you specify the proc sql nothreads; option.

When reading multiple tables, sorting them by the join key should prevent that proc sql generate a sort, and it should read both tables sequentially if the nothreads option is used. Ultimately, the SQL optimiser makes the decision.

Re: Can SAS give different output when we have duplicates in join

Patrick — Wed, 22 Sep 2021 09:45:35 GMT

With SQL the only way to guarantee the sort order is via an Order clause AFTER the join.

Even though due to some SAS specifics there might be some cases where you get the desired sort order also without an order by clause, you never should not attempt to implement this way because it's inherently not how SQL works.