Any SQL command can be passed using the execute ( ) by xxx construct as shown:ĬREATE TABLE. You are not limited to queries, where you use the result as input to SAS. LIBNAME xmanbmf ODBC IGNORE_READ_ONLY_COLUMNS=YES DATAsrc=EXT_Manuel_BMF SCHEMA=dbo So a query copied from Microsoft SQL Server manager will work, as shown in the following example: Everything you write in the connection to xxx ( ) construct is just passed to SQL. That macro may get you started, otherwise hit the doc for either ODBC or OLEDB. We use ODBC if you want to use it (or at least try it) as well, perhaps this may help: I see you have Access to ODBC and Access to OLEDB. And hopefully that aggregation is "small". 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. %libname_sqlsvr(libref=FOO,server=MY_SS_SERVER,port=,database=my_database,schema=dbo)ĭebug your query in SQL Server Management Studio. Options sastrace=',d' sastraceloc=saslog nostsuffix I would write your query as explicit pass through as follows: 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.
You could submit the same query via Powershell (Invoke-Sqlcmd) and return the data to the console (say to pipe to ConvertT-Csv)Īs to your query, what I think is happening is, SUM() is both a SAS and SQL Server function. IOW, it's just passing SQL to your RDBMS (SQL Server). With explicit pass through, SAS is just a client to SQL Server, analogous to Powershell. Note that proc copy or proc append are faster than a data step for simply copying tables.Īlso note that the table creation can be accelerated by setting larger values for options BUFNO and BUFSIZE, and possibly by using Windows's SGIO, if disk I/O is the culprit.Īs others have said, use explicit pass through (esp. What is the issue now? Is it that you'd like these option to be part of the default libname setting? Well done for finding, testing and implementing these options. MYNEWTABLE (BULKLOAD =YES BL_RECOVERABLE =NO DBCOMMIT = 300000 INSERTBUFF = 32767 UPDATE_LOCK_TYPE = TABLE READ_LOCK_TYPE = TABLE ) set MYSASDATA run I see that you found a way to do what you needed: data SAS.
SAS does not write code (except for the implicit pass-through optimisations) and macros do no write code either. I haven't been able to get SAS to consistently write standard code ( I know macro variables and other functions can lead to abnormal code which I often advise folks not to use when talking to SQL).