Since it is 2:30am for Ksharp, and it only takes minor tweak on his original code,  I will just answer that for him. proc sql; create table want as select a.*,coalescec(b.valvar_1,(select valvar_1 from b where l_var1='Z' and l_var2 = 'ZA')) as valvar_1, valvar_2   from a left join b on a.var1=b.l_var1 and a.var2=l_var2 ; quit; Good Luck! Haikuo ... View more

Since I don't have your sample data, I will make one based on my understanding of your description, hopefully it is not completely off. data have; infile cards; input my yymmn6. v1 v2 ; format my yymmd.; yq=cats(year(my),qtr(my)); cards; 200001     1     4 200002     2     7 200003     4     9 200004     5     12 200005     7     14 200006     8     17 200007     10     19 200008     11     22 200009     13     24 200010     14     27 200011     16     29 200012     17     32 200101     19     34 200102     20     37 200103     22     39 200104     23     42 200105     25     44 200106     26     47 200107     28     49 200108     29     52 200109     31     54 200110     32     57 200111     34     59 200112     35     62 ; /*option 1*/ data want (drop=v1 v2 i); do _n_=1 to 3 ;    set have;    array v(*) v1-v2;    array q(*) q_sum1-q_sum2;      do i=1 to dim(v); q(i)+v(i); end; end; output; call missing (of q_sum1 q_sum2); run; /*option 2*/ proc sql; create table want as select yq, sum(v1) as q_sum1, sum(v2) as q_sum2 from have group by yq; quit; I hope you can get a bit of taste of it and apply the logic to your real life data. Good luck! Haikuo ... View more

Do you have a timestamp variable for each month? what does it look like ? such as '2000-01'? You could have numerous approaches. 1. For one, you could just do a double loop using data step, one for quarter and sum it up through all of your records. given you have multiple variables of interest, you may need an additional array loop to save your coding labor. 2. For two, depending on your timestamp variable, you could introduce quarter indicator using some format (such as QTRw.), and concatenate with year to make groups. After that, you can choose many procs, such as, proc summary, proc report, proc sql to accomplish your job. Regards, Haikuo Just Edited. More detail would be appreciated, preferably sample data, both have and want. ... View more

OK, I did some quick research, here is something form SAS help: Memory Limit for the LAG Function When the LAG function is compiled, SAS allocates memory in a queue to hold the values of the variable that is listed in the LAG function. For example, if the variable in function LAG100(x) is numeric with a length of 8 bytes, then the memory that is needed is 8 times 100, or 800 bytes. Therefore, the memory limit for the LAG function is based on the memory that SAS allocates, which varies with different operating environments. It seems to me that if compiler has encountered lag() or dif(), it will allocate more memory storage for the excution. if not, maybe just ONE record at ONE time. Regards, Haikuo ... View more

Well, I don't really know the definite answer to your question. I tends to believe there is a buffer that holds more than just ONE record, otherwise it is hard to explain functions like lag(), dif(). I am sure someone on the forum will help us out. Haikuo ... View more

Well, there are something in your code I don't quite undertand. if a and not b then       do;          DIAS_ATRASO_ATUAL = DIAS_ATRASO_0;          RISCO_TOTAL_ATUAL = RISCO_TOT_ATUAL;       end; DIAS_ATRASO_0 and RISCO_TOT_ATUAL only exists in 'have2(in= b)', when you 'if a and not b', DIAS_ATRASO_0 and RISCO_TOT_ATUAL will always be missing. So is this a typo? Same thing happens to the code downstairs as well. Any way, what you were asking is quite doable using a combination of joins and unions in SQL. I have attached a sample code using sashelp.class to show the basic ideas, and hopefully can help you tackle your real life data. data first15 last15; set sashelp.class nobs=nobs; if _n_<=15 then output first15; if _n_>=nobs-14 then output last15; run; proc sql; create table want as select distinct * from (select a.*, (a.age+b.age) as n_age from first15 a, last15 b where a.name=b.name) union (select a.*, (a.age*10) as n_age from first15 a where a.name not in (select name from last15)) union (select b.*, (b.age/10) as n_age from last15 b  where b.name not in    (select name from first15))    ;quit;   Regards, Haikuo ... View more

Well, if you want to stick to your original design, I think you already have a pretty good idea on the up-limit to your hardware. So you can definitely use that info to break up your one task into many and finish it up. I am also curious about the reason you need so many tables. if you don't mind sharing the whole picture, maybe someone on the forum can figure out an alternative way to deal with it without producing 30,000 tables. Regards, Haikuo ... View more

I don't know the up limit of how many tables you can generate on one pass. But I concur with Tom. You should be able to solve your problem by using his suggestion. Of course, it may not as efficient as your original plot though. just my 2 cents, Haikuo ... View more

You would need to share more code to the boundary of your Macro definition. %do does not work this way (open code). Regards, Haikuo ... View more