FG, Based on your reply, it seems like the Network bandwidth is your bottle neck!! The fact that you are using NAS to host your data set, that means every time you read the data set, you are putting your SAS process under the mercy of the Network, and hoping there is adequate bandwidth left to transfer the data down to your Desktop, so the SAS process can read it! Regardless whether you add Indexes or not, I'm not sure how much difference it will make!! Unless somebody from your company's Network Infrastructure increase (if even possible) the size of the network pipe between your Desktop and the Storage Disk, you'll not see any significant performance increase! In my humble opinion, your best short-term solution right now would be: 1. Use multiple directly attached disks (the more spindles, the better) 2. Utilize SPDE and spread the Data Paths across the multiple disks (avoids I/O contentions) 3. Use proper -memsize -sortsize SAS startup options settings long-term: Move to Server SAS Install and SAN 🙂 Good luck, Ahmed ... View more

Hi Chris, OK, I agree with you in regards to the Disk Array benefits. But I was not saying SPDE is faster than Disk Arrays. Based on FredGill's messages Disk Array are not an option, as this is a Desktop SAS installation he is dealing with! Having said that, adding SPDE for Large data sets such as the one mentioned here, even if it was stored on optimized Disk Array, would surpass the performance of the SAS Base Engine due to the following reasons: supports more than 32K columns in SAS 9 and later. The base engine supports more than 32K columns in SAS 9.1 and later. is the only SAS engine that supports more than 2 31 - 1 (approximately 2 billion) rows on 32-bit hosts. supports the implicit sort for BY processing. supports optimization of the WHERE expression with multiple indexes.  (BASE Engine supports single Index) supports optimization of the WHERE expression containing OR. Hope this clarify the response to you and others, Regards, Ahmed ... View more

Fred, You can find information about SPDE here http://support.sas.com/rnd/scalability/spde/index.html Check out the Setup link http://support.sas.com/rnd/scalability/spde/setup.html From Reading your original message, I'm guessing you are running on Windows 7 (64 bit?) Desktop with (Dual/Quad) Core and 8 GB memory, with Single Hard Drive. Please correct me if my conclusion is wrong. SPDE is best suited when you have Multiple CPUs & Hard Drives, because it's all about threading and I/O. Back to your performance issues. I don't recall the OS you are running your SAS jobs on. Having Memsize set to 0 is not always a good option!! You are better of specifying a reasonable number ( x MB/GB) pending on your available memory, so you can somewhat avoid causing swapping and negatively affecting your OS and Server. Using CLASS in your Proc Summary/Means will try to load all the unique Combinations of your specified variables into Memory. (Bad Idea) Using BY in your Proc Summary/Means will utilize the Sorted Order/ Indexes you have in place. If all fails, I'm guessing your only option is Divide and Conquer approach. Split the data into small Chunks,  Summarize, Append. Once all chuncks have been summed and appended, re-summarize for your grand total figures. Hope this helps, Ahmed ... View more

9.2 docs are available here: Windows: http://support.sas.com/rnd/emi/EbiApm92/sas92.ebiapm.win.pdf Unix/Linux: http://support.sas.com/rnd/emi/EbiApm92/sas92.ebiapm.unix.pdf 9.3 docs are available here: http://support.sas.com/demosdownloads/setupcat.jsp?cat=SAS+Audit%2C+Performance+and+Measurement Follow the link, you should see a table like below. it has a "Request Download" button for the docs. SAS Audit, Performance and Measurement Package Platforms     Description     File Name     Size     Release Date      All     APM 9.3 Documentation     APM93.pdf     2 MB     2012-09     Request Download ... View more

Did you download 9.3 or 9.2? ... View more

Guys, Here is where you can find info about the SAS 9.x Audit and Performance Measurement package http://support.sas.com/rnd/emi/index.html Under the "Hot Topics" column on the right, you should find 9.3 & 9.2 package links. From there you'll know where/how to get the software bundle plus the installation documentation. Good luck, Ahmed ... View more

The issue is not related to the memory on your server, but rather to the amount of memory allocated for the SAS process! run the following code, to see how much memory was allocated to your SAS session. Proc Options group=memory; run; Look for the memsize option Ahmed ... View more

Try this /* SYSPBUFF: Automatic Macro Variable that Contains text supplied as macro parameter values.*/ %Macro GetFundData/parmbuff;     %put Syspbuff contains: &syspbuff;     %local l_pCount /* Parameter Count */                l_pi        /* Parameter Index */                l_pName; /* Parameter Name */     %let l_pCount=%eval(%sysfunc(countc(%superq(syspbuff),%str(,)))+1);     %put l_pCount=&l_pCount;     %do l_pi=1 %to &l_pCount;         %let l_pName=%scan(%superq(syspbuff),&l_pi,%str(%(,%)));         %put l_pName=&l_pName;          /* You can insert additional code based on the current parameter */     %end; %Mend GetFundData; %GetFundData(x,mfFunds1-mfFunds14,VDate1-VDate14); %GetFundData(x,mfFunds1-mfFunds14); %GetFundData(mfFunds1-mfFunds14); ... View more

Hi Liej, Try to approach this in a different way. Use Data Set Hash Object to load the BankList and use it as a Lookup table. You'll need two instances, with different Key variable in each (one with Rek1 as key variable,  second with Rek2 as key variable). Read in your Execel file RV.xls once and try to check your lookup variables (BBAN,IBAN) against the two Hash Object. There are several examples of how to use the Hash Object, - Go to www.lexjansen.com and Search for Hash Object - Go to support.sas.com and Search for Hash Object Good Luck ... View more

You probably would have to break the query into multiple queries/tables ... View more

I would recommend checking the "Maximizing Teradata Load Performance" section of the SAS/ACCESS Interface to Teradata user guide for alternative options ... View more

Yes, Your SQL looks like Oracle's SQL syntax. As far as I know, SAS SQL does not support the "Over" syntax. ... View more

A manual approach is possible but it will have its flaws and you'll have to develop a really robust application Interface to Minimize/Eliminate pitfalls. Check the LOCK Statement in the SAS Online Help Docs, it's available part of BASE SAS & SAS/SHARE. Having said that, even if you built some sort of Graphical User Interface, to manage your Data Set(s), there is no way you can stop users from access the Data Set(s) directly from within SAS Explorer Window, or even from the Data Step/SQL statements!!! Hence your best bet, is to have your volatile Data Set(s) that could be modified by multiple users managed by SAS/SHARE Server/Library definition, and this way, you'll be covered all around regardless where the data get access from. just my 2 cents, Ahmed ... View more

You need to use SAS/SHARE for concurrent user updates. It was developed to handle such requirements. ... View more

Try this, data chars;     length chr1 char2 char3 $8;     chr1='23' ; char2='45' ; char3='78' ; output;     chr1='44' ; char2='66' ; char3='99' ; output;     chr1='11' ; char2='22' ; char3='88' ; output; run; proc sql noprint;     select    strip(put(count(*),best.))     into    :g_numCount     from    dictionary.columns     where    libname='WORK'     and        memname='CHARS'     and        type='char'     ; quit; %put &g_numCount; data nums (keep=numvars1-numvars&g_numCount);     set chars;     array charvars (&g_numCount) $ _character_;     array numvars (&g_numCount) 8 numvars1 - numvars&g_numCount;     do i=1 to dim(numvars);         numvars=input(charvars,best8.);     end; run; ... View more