How about after: PROC FORMAT CNTLIN=SPECDESC; RUN; %END; insert: %ELSE %DO; PROC FORMAT; VALUE $DES; RUN; %END; Tom ... View more

You could check out the following link, which references a section in the SAS documentation that covers combining SAS datasets. It's powerful, but not like any other language out there (quite different from SQL). Tom http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p15jvywi5avt3cn1bee8r6c33ux1.htm ... View more

Unfortunately, running SAS on a UNIX server connecting to MS SQL Server, which by definition has to run on Windows, is one of the more challenging combinations. I agree, that with you moving to UNIX servers for SAS, it's not worth digging into the bulk loading options. I'm not an expert on SQL Server, but gsnidow's approach looks very similar to what the bulk loader does, so if it's easy to implement it might really work well for you. If not, Patrick's suggestions to check those setting is worth following up with. Changes to them certainly might help. And last, fly this by SAS Tech. Support. They may be aware of other approaches; surely this comes up fairly often. Good luck!   Tom ... View more

Yes, with the "insert" statement method, sometimes I think I can actually see the records moving over the wire from the SAS server to the database server. 😉 In terms of UNIX: - What is your DBMS? - Is your SAS server moving from Windows to UNIX? - If your DBMS isn't MS SQL Server, is it currently on Windows, and if so, will it be moving to UNIX? Tom ... View more

Check out "Bulk Loading for ODBC" in the SAS documentation. When you use the first pattern for copying a SAS dataset to a table in a SQL database, SAS implements it via a series of SQL "insert" statements. This, as you have noted, is very slow! I don't believe that your pass-through attempt will work, because when your database receives "from bw.sastable", it won't know what that is, as it's a SAS object. Transferring data from SAS to a DBMS is a common problem, and to resolve it SAS has added special options to SAS/Access that will write the data to a flat file, and then call the DBMS bulk loader facility to load it to the DBMS. This should speed things up by 10 to 100 times. Tom ... View more

I would like to suggest you consider using Enterprise Guide if you intend to run SAS on your PCs connecting to your server sessions via SAS/Connect. Enterprise Guide can take a lot of the fiddling around with connection parameters off the table, and make submitting to a server faster. Tom ... View more

I don't think so. If I understand it correctly, passthrough will send the code directly to a (non-SAS) database server, which will execute the code. I can't see how your database server would know what to do with a SAS dataset. You are absolutely correct that it may be much more efficient to reduce the size of the returned data in the  database server. If your SAS temp dataset is just a list of join values that you want to select from your big table, consider putting them into a macro variable, which you can use as WHERE var IN(&macrovariable). This might quite fast. I have the feeling there's a SAS/Access option that will tell SAS to do this automatically, but I can't find it offhand. (Anybody out there know the name of this?) Another option is to load your SAS temp dataset to the database, and then you can implement the join as a passthrough. Tom ... View more

I suggest throwing this one to SAS Tech Support; I think they'll have fun with it. Tom ... View more

SASKiwi's answer is completely correct; I'll just add a couple of comments. Since you mentioned that you have access to Enterprise Guide, I'm assuming that your client is comfortable with it. In that case, setting up EG so that the components (code, results, log...) generated by the tasks will be kept within the project leads to a nice, clean project file to deliver to the client...everything is saved within the project, except for the actual SAS datasets, which are represented by links. Therefore no data exposure concerns. I think you'll find over time that the EG representation of an ordered list of tasks leading to results is a nice way to represent the code (I note from your comment that you find it a little disorganized looking at this point!) I agree absolutely that for reliable, production-grade scheduling of a job using the EG scheduler is not the best solution. In that case, you should definitely export the SAS code and use one of the server-based options. And of course, with SAS, there are ten ways to do anything, but these should get you started. Have fun! Tom ... View more

I have a very strong opinion on this. As a new user, you'll find it easiest to use Enterprise Guide. In a new EG project, bring in your SAS dataset, and then use the Query Builder to do your extracting and grouping (it uses PROC SQL in the background). There is a "send email" task in EG, so depending on how complex your requirements are, you may be able to do the whole thing with very little fuss. You can then use the ability to schedule your EG project to run it daily. Be careful about macros; they aren't the same thing in SAS that they are in other products. In SAS, they are incredibly powerful, but can be very difficult to understand and use. As you figure out exactly what you're trying to do, feel free to post more questions! Tom ... View more