Before I could release the code, I had to sterilize it, the names should be consistent representations of the original variables and each are surrounded by "<>" to denote that they are the sterilized names.   The code is pretty simple, but it does our processing very fast:   proc sql; create table <localFlagSource> (replace=yes) as select <elementType>, <flagName> from <serverDataSource1> order by <elementType>; Create table <localFormulaSource> (replace=yes) as select <formulaType>, <SASFormula> from <serverDataSource2> order by <formulaType>; quit; %let <elementTypeMacroVar>=; %let <flagListMacroVar>=; %let <formulaSourceMacroVar>=; %let <formulaMacroVar>=; proc sql NOPRINT; select <elementType>, <flagName> into :<elementTypeMacroVar> separated by ' ', :<flagListMacroVar> separated by ' ' from <localFlagSource> order by <elementType>; /* separated by nothing is correct for loading the <formulaMacroVar> to pack the formuals together--each is terminated by a semicolon */ select <formulaType>, <SASFormula> into :<formulaSourceMacroVar> separated by ' ', :<formulaMacroVar> separated by '' from <localFormulaSource> order by <formulaType>; quit; %let <formulaMacroVar>=%SUPERQ(<formulaMacroVar>); %putTime; data localSAS.test (replace=yes); <calculatedField>=0; array flag[*] &<flagListMacroVar>.; retain &<flagListMacroVar>.; set localSAS.<localSource> /*(obs=100000000)*/ end=eof; by <uniqueKey>; if first.<uniqueKey> then do; /* blank our in memory array for each new grouping */ do i=1 to Dim(flag); flag[i]=0; end; end; flag[findw("&<elementTypeMacroVar>.", strip(put(<elementType>, best.)), ' ', 'E')]=1; /* reconstitue our data array in memory only */ if last.<uniqueKey> then do; &<formulaMacroVar>.; /* entire list of formulas to process by each true clause contains and output */ end; keep <uniqueKey> <calculatedField>; run;   The few lines of the datastep are where we do the processing, it may be a little misleading: there are approximately 400 formulas that load into formulaMacroVar and are expanded into the code for in memory processing through each loop. Our data array is only reconstituted in memory by using the macro variable that contains a list of the variable names and elements are set appropriately in the lookup to allow the formulas to work--each formula looks for a 1 in elements used for calculation purposes.  The list is cleared at the beginning of each data group in the source file of the datastep.     The processing is done locally on my workstation: Processor Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz, 2394 Mhz, 10 Core(s), 20 Logical Processor(s) and processes through our source in the datastep at the rate of about 12.7 Million observations per minute. ... View more

In a project I am coding, we ran into a problem where we have billions of rows of data that we need to make into a wide table, do some processing on it, then have the data written out the db.  The size of the problem makes it so we literally would be processing for days just in the first step of reconstituting the file, but we decided to go a different way:   1 sort the data that we need to process. 2 make in memory arrays to hold the data, for you it sounds like you need something by months, we had a much wider need 386 elements wide. 3 process the dataset by your unique identifier that you sorted by 4 output only the data you need for your answer.   we came up with some pretty ingenious ways of looking at the problem and it works in a datastep.  Instead of days and days of processing, out complete answer is done in 8 hours.  We massage 6.7 Billion rows of data in memory to achieve our answer. We process 12.7 Million rows per minute. ... View more

This SQL will pull for a specific year/month combination. I think that is what you needed, but one thing to check: our SQL db has DateTimes and Dates, make sure of which one you are working with before using the "datepart" function.   proc sql; create table tmp as select myDate from myTable where put(datepart(myDate), yymmn6.)='201708'; quit; ... View more

jc, I can verify what Tom Kari said, being an Oracle Db I see this happen a lot. When the network pipe is full, the network pipe is full, you just have to wait due to bandwidth limitations. If your server is maxing out trying to pull your data, you cannot get more down the pipe, even if it is open, than the server will push. As stated by others, check with your admins to see if this is the case, but here is an idea to consider: "Can any of your data be considered static?" If it can be, then you should consider storing static portions locally; thus, not having to pull at least part of the data through the network pipe or server. When you need a fresh copy of the data, then delete the dynamic content and only pull data that is not considered static. You'll need to check with your IT/Security managers to see if storing the data locally is an acceptable option for your location. ... View more

If you have a full working SAS program, you can "easily schedule it" thought the Windows scheduler by making a small batch file to launch it. In the batch file you need to put your fully qualified path to your SAS executable (the SAS program) followed by your fully qualified path to your SAS configuration file followed by the option "-sysin" and then followed by your fully qualified path to your SAS program. here is a small example of the batch file: echo on "C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.4\SASV9.CFG" -sysin "C:\Users\myID\Documents\My SAS Files\9.2\someFolder\mySASFile.sas" Note: in the example the quotes are necessary for windows to understand the path due to spaces. Once you have that, run the batch file to make sure it works, then use the Windows scheduler to setup the schedule for the run. Good luck, I use this a lot to schedule large jobs that take multiple hours to run so I don't have to be there to launch them. ... View more

Please forgive the unchecked code, but I'm just about out the door for the night. data completed_calls; set myData; if call_code ^= 'CS' then delete; keep ID; run; proc sql;   create table myAnswer as     select ID, count(1) as call_count from (select t1.ID from myData t1 join completed_calls t2 on t1.ID = t2.ID) group by ID; quit; Assumption: call_code = 'CS' will show up on a valid call ID line, if not, then just subtract 1 from the count in the SQL Code. You may have to create your destination table for the sql  first depending on your data source, then use a insert/select sql statement. ... View more

As ballardw said: if it needs to be shared, then a DBMS makes a lot of sense, and IO is always a problem. We run sas against datasets with hundreds of millions of observations and it is shared data, so it is in an RDBMS. If I am very careful and make appropriate limitations in where clauses and there are enough indexes on the data on the server, then I can greatly shorten the amount of time I have for the basic data to come down to my workstation. A large server can be very beneficial in cutting your query time, but you have to program to take advantage of the resources of the server to make it work for you. On the other hand, every time I want data for any type of local processing, I have to wait. I also have to manipulate the data from a normalized form into a wide table format, dataset, so the process I want to do can be carried out effectively; he basic IO for this may be hours during my process run. Realistically though, any time I have to get data from a dataset not on my machine, I have to wait on network traffic. So even if the data is not on the server, then I am still going to have IO across the network from a repository of our SAS datasets. Network based data actually takes more time than requesting data directly from the RDBMS. Those are the pros and cons I work with on a daily basis, there are many more, but these are the major ones I work with every day. ... View more