CODEWITHSUNDEEP
sqlms-accesssasproc-sql
esapeno
esapeno

Reputation: 3

Fixing "The query requires remerging summary statistics back with the original data" in SAS. Converting Access SQL to Proc SQL

Beginner here. I am attempting to convert some inherited Access queries into SAS but am running into an issue of returning more records in SAS with many duplicates vs running in Access that appear to be an issue of grouping and summing certain variables. In SAS I am met with the warning: "The query requires remerging summary statistics back with the original data" in SAS.

Would like to know what is a better way to rewrite the Access SQL code as I am more or less just copying and changing it to work via Proc SQL in SAS. Believe the issue occurs at the creation of variables starting with the name "SumofSum" in ComboQuery, and a difference to how they are treated in Access vs SAS.

ACCESS SQL

Rev_Query

SELECT
       [Rev].Contract,
       [Rev].[Posting Date],
       [Rev].[Documnet Number],
       Sum([Rev].Amount) AS SumOfAmount,
       Sum([Rev].[Reclass Amt]) AS [SumOfReclass Amt]
FROM   [Rev]
GROUP  BY 
       [Rev].Contract,     
       [Rev].[Posting Date],   
       [Rev].[Documnet Number];

Remit_Query

SELECT 
       [Remit].[ID],
       Sum([Remit].[CCHS Amt]) AS [SumOfCCHS Amt]
FROM   [Remit Data]
GROUP  BY 
       [Remit Data].[ID];

ComboQuery

SELECT 
       [Rev_Query].Contract,
       Sum([Rev_Query].SumOfAmount) AS SumOfSumOfAmount,
       Sum([Rev_Query].[SumOfReclass Amt]) AS [SumOfSumOfReclass Amt],
       Sum([Remit_Query].[SumOfCCHS Amt]) AS [SumOfSumOfCCHS Amt],
       Sum(Round(Nz([Rev_Query] ! [SumOfReclass Amt]) + Nz([Remit_Query] ! [SumOfCCHS Amt]), 2)) AS Expr1
FROM   [Remit_Query]
RIGHT JOIN 
       [Rev_Query]
ON 
       [Remit_Query].[ID] = [Rev_Query].[Documnet Number]
GROUP  BY 
       [Rev_Query].Contract
HAVING 
       (((Sum(Round(Nz([Rev_Query] ! [SumOfReclass Amt]) + Nz([Remit_Query] ! [SumOfCCHS Amt), 2)) ) <> 0 ));

SAS

Rev_Query

proc sql;
create table hw.Rev_Query as
select 
   t1.Contract,
   t1.Posting_Date,
   t1.Document_Number,
   sum(t1.Amount) as SumOfAmount,
   sum(t1.Reclass_Amt) as SumOfReclassAmt
from hw.Revenue as t1
group by 1,2,3;
quit;

Remit_Query

proc sql;
create table hw.Remit_Query as
select 
   t1.ID,
   sum(t1.CCHS_Amt) as SumOfCCHS_Amt
from hw.Remit as t1
group by 1;
quit;

ComboQuery

proc sql;
create table hw.ComboQuery as
select 
   t1.Contract,
   sum(t1.SumOfAmount) as SumOfSumOfAmount,
   sum(t1.SumOfReclassAmt) as SumOfSumOfReclassAmt,
   sum(t2.SumOfCCHS_Amt) as SumOfSumOfCCHS_Amt,
   sum(t1.SumOfReclassAmt,t2.SumOfCCHS_Amt) as Expr1
from hw.Remit_Query t2
right join hw.Rev_Query t1 on t2.ID = t1.Document_Number
group by 1
having Expr1 <> 0;
quit;

Upvotes: 0

Views: 468

Answers (2)

Richard
Richard

Reputation: 27508

Try

(sum(t1.SumOfReclassAmt) + sum(t2.SumOfSumOfCCHS)) as Expr1

If you want missings to be zeroes, which you might need if a column is all nulls, try

(
  sum(coalesce(t1.SumOfReclassAmt,0)) 
+ sum(coalesce(t2.SumOfSumOfCCHS,0))
) as Expr1

Wouldn't hurt to also change <> to SAS ne (not equal) operator/mnemonic.

Upvotes: 0

Tom
Tom

Reputation: 51621

The function SUM(a,b) you used in the last query is NOT the SQL function SUM() that aggregates across observations. That is why you are getting the remerging.

I have no idea what the heck the gibberish you have in the ACCESS query means:

Sum(
  Round(Nz([Rev_Query] ! [SumOfReclass Amt])
      + Nz([Remit_Query] ! [SumOfNet Amt To CCHS])
      , 2
       )
)

But is does not look like it is just trying to add two numbers together which is what SUM(a,b) means.

SAS does not have an NZ() function. Figure out what that does in ACCESS and map that to something. Perhaps you can replicate it with a CASE clause. Or perhaps it just a COALESCE() function. Perhaps you could use the goofy IFN() function SAS added recently.

Also SAS has a ROUND() function, but I doubt that you would want to round to even integers which is what ROUND(X,2) would do in SAS. Most likely in ACCESS that means round to 2 decimal places which in SAS would be ROUND(x,0.01).

Perhaps:

  sum(round(sum(0,t1.SumOfReclassAmt,t2.SumOfSumOfCCHS),.01)) as Expr1

Upvotes: 0

Related Questions