Do you mean having two visits in a row missing (case 102), or all visits missing (you don't have a case for this). Tom ... View more

Alternately, here's some advice on increasing your memory, in case that helps. Memory is allocated at the start of your SAS session, so you can't increase it during the session. You have to increase it before SAS starts. First, make sure you know which memory options need to be increased. Second, check what they are currently (you can do this by running PROC OPTIONS). Next, set up a new shortcut to SAS. Take the startup line out of your exisiting SAS program start icon, it should look something like this: "C:\Program Files\SASHome\SASFoundation\9.3\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg" Add your new memory option(s), so it looks something like this: "C:\Program Files\SASHome\SASFoundation\9.3\sas.exe" -memsize 6G -CONFIG "C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg" Start SAS using your new shortcut, and check what SAS is using for memory using PROC OPTIONS. It should reflect your new value. Tom ... View more

Okay, here we go! It's quite a few steps, but each step is fairly simple. That's one of the big differences between Enterprise Guide and writing SAS code, it's easier to use many small steps. (I'm giving the first date in your first table the name "InvoiceDate", since you didn't name it). Step 1 ------ Create at query on your first table (with payments). The main thing you want to do here is to create a new computed column, let's call it BigDelay. In a CASE function, set it to 1 if PaymentDate - InvoiceDate > 60, else 0. Also, filter by BigDelay = 1. This should i) figure out which records have a big delay, and ii) only keep those records. Step 2 ------ Run a rank task on the result. Use InvoiceDate as your "column to rank", and IDClient as your "Rank by" column. This will give the resulting rank 1 to the minimum (earliest) value of InvoiceDate. Step 3 ------ Run a query on this output, selecting only the records where the rank equal 1. This should give you the earliest big delay for every client. Step 4 ------ Another query. Use the result of the above step, but add your second table (I'll call it Table2) to the query. Make sure the two tables are joined on IDClient. Add a filter of "where PurchaseDate < InvoiceDate". I can't tell, but you might also need an "AND PurchaseAmount NOT IS MISSING". This will give you all of the Table2 records with a date before the cutoff date from Table 1. Step 5 ------ Run a rank task on the result. Use PurchaseDate as your "column to rank", and IDClient as your "Rank by" column. This time, in the "Options" tab, turn on "Reverse ranking...", which will rank the LATEST PurchaseDate as number 1. Step 6 ------ And finally, another query on the result, again selecting the records with rank equal to 1. This should give you a table with the record for each IDClient that meets your criteria. I know it sounds like a lot of work, but once your familiar with these tasks it would only take about ten minutes. Let us know how it goes,   Tom ... View more

This step will create a 1-record table with your totals: PROC SQL; CREATE TABLE want_totals AS   SELECT   (SUM(TotalPoss1)) AS AllPoss1,    (SUM(Totalmet1)) AS AlltotalMet1,    (SUM(Totalmet1) / SUM(TotalPoss1)) FORMAT=Percent6. AS AllScore1,    (SUM(TotalPoss2)) AS AllPoss2,    (SUM(Totalmet2)) AS AlltotalMet2,    (SUM(Totalmet2) / SUM(TotalPoss2)) FORMAT=Percent6. AS AllScore2   FROM WANT; QUIT; And then this will merge your totals back into your original dataset: data want_with_totals; merge want want_totals; run; Tom ... View more

Hi, Cesar Could you clarify something? For Client 436968, you also have a "big delay" a bit further down from 15JUN2011 to 11SEP2011. Do you want to use the dates to determine the first delay, or the order of the records? If it's the dates, should it be the start date or the end date that determines it? And, what to do about ties in one of the dates? Tom ... View more

How many columns and rows are in your SAS dataset that is causing the failure, and how many MB is it? I'm trying to replicate your problem. Tom ... View more

So it looks to me like AllPoss1 is the sum for all rows of TotalPos1, and AlltotalMet1 is the sum of all rows of TotalMet1, and AllScore1 is just AlltotalMet1 / AllPoss1. And the same for the ...2 variables. For these six variables, do you want them on every row of your data, or only on the first row? Tom ... View more

I agree. I think your only option is to collapse the data by the first three or four strings, and from that derive a table of "make" values. After extracting that, the remainder becomes the "model". There are just too many odd possibilities to automate the detection. However,one optimization might be to determine if there are only two words, and if there are assume that they are make and model. The makes extracted from that can then be used against the remainder, and any matches can be handled in an automated fashion. This should leave a very small residue for manual evaluation. I've dealt with the same problem with municipality and province, and there's really no count-based solution. Tom ... View more

Could you show us exactly what your proc means step looks like? I'm wondering if you're using VB1 to VB3 as analysis variables or classification variables. Either way, you're not coding it exactly right to get the results you want. Tom ... View more

My advice is to get your connection problem resolved; at this point, I would put in a case to SAS Technical Support. I've had to go through a number of these over the years, and they're very good. I'm sure it should work the way you want it to, so why bother with workarounds? Tom ... View more