SELECT
A,B , SUM(PAID) AS PAID
FROM X
WHERE A between 2000 and 3000
GROUP BY
A,B
As others have said, use explicit pass through (esp. if you don't use SAS much). With explicit pass through, SAS is just a client to SQL Server, analogous to Powershell, .Net, SSMS, Toad, etc. IOW, it's just passing SQL to your RDBMS (SQL Server). You could submit the same query via Powershell (Invoke-Sqlcmd) and return the data to the console (say to pipe to ConvertT-Csv)
As to your query, what I think is happening is, SUM() is both a SAS and SQL Server function. If a SAS implicit pass through query contains a SAS function, formats, etc, a more generic query is sent to the RDBMS, then SAS does the aggregation.
I would write your query as explicit pass through as follows:
* set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options dbidirectexec;
options msglevel=I;
options fullstimer;
* allocate SQL Server libraries ;
%libname_sqlsvr(libref=FOO,server=MY_SS_SERVER,port=,database=my_database,schema=dbo)
proc sql;
connect using foo;
create table blah as
select *
from connection to foo (
SELECT A
,B
,SUM(PAID) AS PAID
FROM X
WHERE A between 2000 and 3000
GROUP BY
A
,B
);
quit;
Debug your query in SQL Server Management Studio. The explicit pass through should then have similar performance as via SSMS, barring the network traffic to send the aggregated data back to SAS to create the data set. And hopefully that aggregation is "small".
I see you have Access to ODBC and Access to OLEDB. We use ODBC; if you want to use it (or at least try it) as well, perhaps this may help: https://github.com/scottbass/SAS/blob/master/Macro/libname_sqlsvr.sas
That macro may get you started, otherwise hit the doc for either ODBC or OLEDB.
Hope this helps...
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
