SAS may be the best answer to "query huge amounts of data in sometimes rather odd ways". Using SQL Server for storage is fine, but not using anything else in front of it (SSAS is useless) is bringing a knife to a gun fight. Trying to do everything in a relational way means tying a hand and a foot behind your back. The real world doesn't neatly fit the model, hard as you might try to make it, so performance suffers greatly and doing unusual ad hoc things takes longer to figure out. Get SAS to send pure relational operations to the DBMS to do but perform other operations within. SAS's own SQL engine gives the user much more convenience since it supports SAS's functions and macro language, far richer than plain DBMS's, but I haven't found it to be particularly quick. In interoperability, SQL Server continues to improve, but SAS still works better with many more other applications. It has always been a best choice for moving data around. Organizations often choke on the licensing model, since most do "capital investments" every few years instead of paying a "licensing fee" every year (hefty, but does include some of the best support going). All this was about plain SAS. SAS/BI is really the product SAS will try to sell you to do what you describe; I haven't used it so can't rate it.
As far as those comments about writing code with SAS being "terrible", well, it can be inconsistent, but mostly those people have just never grasped the somewhat unique models it uses of handling observations. I find T-SQL to be seriously lacking for many tasks. If going all-MS, get VS and use a regular procedural language along with SQL Server.