Hi.   Here's another way of doing it, just using algebra.   data want;      set have end=done;      drop _:;      _cumulative + value; * sum value;      cumulative=min(70+(done*_cumulative),_cumulative); * minimize to 70 or not if done;      _cumulative=_cumulative-int(cumulative/70)*70; * subtract if > 70; run;     min function will return the minimum value and int will returns the integer part.   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

Hi.   I'm sorry but I didn't get it... could be me.   You skipped the first row while importing the file into a table1 (for some reason), thus not using the variable names from the title row of file.   But after that you still want to name the variables of table2 (which is table1 transformed) from the title row of the file?   Daniel Santos @ www.cgd.pt ... View more

Hi.   For problem 2:   make the xml_test_&j dataset a static name, like this:   ...   data _xml_test;      set my_xml.xml_contents; run; *xml_test contains the parsed xml file. One record per xml file; PROC APPEND BASE=xml_test DATA=_xml_test. FORCE; RUN; %end;   ...   This will overwrite the dataset for each cycle, so you will not need to delete it.   For problem 1:   from your code the output dataset is overwritten not appended I can assure you that, what is not overwritten is the auxiliary xml_test dataset in the WORK library. It's always appended with the contents of the xml_test_&j datasets... So by not deleting it in the beginning of the macro and running the macro several times in the same SAS session (same WORK library) you are appending everytime the result of your current run to the previous one... Since at the end you are generating the output dataset from xml_test, here's why you have that append effect.   To solve this, you have to delete the xml_test dataset just before cycling through the files, like this for example   ...   *check macro variables for the xml files - outputs value to log; %put count of xml files is &xmlNObs; %put name of xml files is &xmlname1;   * delete xml_test; proc datasets lib=work nolist;      delete xml_test; quit;   ...   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

  As @Tom pointed out, the correct approach to circumvent the problem would be to remove the direct references to the macro var and replace them with the symget function (the former is resolved at compile time, the latest at runtime).   This will suffice:   data want;      set Colour_text;      if prxmatch("/[a-z]*("!!strip(symget('NAMES'))!!";)( car| ball| yacht)/i",text); run;   This will solve the problem of the yellow yacht/(not yellow) yacht and you'll be good with a prxmatch expression up to 32K in length... But, I would not use this kind of approach with a lot of data as it's probably not the best performance wise (regular expresions are quite CPU intensive)... Give it a try to see if it works well for you.   More on symget: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202938.htm   Hope it helps.   Daniel Santos @ www.cgd.pt     ... View more

Hi MR_E.   It's actually a bit simpler for your case, here's the code modified to keep rows which will match the parse.   * load colours names into list; data _null_;      set colours;      call symput('NAMES',catx('|',symget('NAMES'),upcase(NAME))); run; %put NAMES=&NAMES;   * keep matches; data want;      set Colour_text;      if prxmatch("/[a-z]*(&NAMES;)([a-z]| car| ball| yacht)/i",text); run;   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

Hi.   Give a look at my last solution proposal on this topic:   https://communities.sas.com/t5/Base-SAS-Programming/Hide-the-name-inside-the-text/m-p/321836#U321836   That's exactly what you need, just remove the masking code.   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

OK ghastly_kitten, you're right I missed this requirement, as long as there is no duplicates I think it's just a matter of throwing firmid in a cross join and adjusting the calculation to exclude those records, like this: proc sql noprint; create table want as select a.portid, a.firmid,        (b.sumreturn-c.sumreturn+return)/(b.cntreturn-c.cntreturn+1) as avgreturn        from        return as a,        (select portid, myear, sum(return) as sumreturn, count(return) as cntreturn                from return group by portid, myear) as b,        (select firmid, myear, sum(return) as sumreturn, count(return) as cntreturn                from return group by firmid, myear) as c where a.portid ne b.portid and a.myear eq b.myear and a.firmid eq c.firmid and b.myear eq c.myear; quit; Which will produce this, which I believe is right: portid firmid myear return avgreturn 1 120 200301 3.12 -0.6425000 1 130 200301 2.13 -0.6425000 1 100 200301 2.11 -0.6425000 1 140 200301 3.67 -0.6425000 1 120 200302 6.81 3.2816667 1 130 200302 2.55 3.2816667 1 150 200302 3.21 3.2816667 1 140 200302 4.21 3.2816667 1 100 200302 5.23 3.2060000 2 330 200301 -2.13 2.7575000 2 300 200301 1.11 2.7575000 2 340 200301 -1.67 2.7575000 2 320 200301 0.12 2.7575000 2 300 200302 4.23 -0.6425000 2 320 200302 3.81 4.4020000 2 350 200302 4.23 4.4020000 2 340 200302 2.21 4.4020000 2 330 200302 1.55 4.4020000 2 100 200302 3.66 4.1950000 Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Hello. If I got this right, it's just a matter of summing/counting the data by porid, myear and merge back with the original dataset with the correct join clause. For this kind of data volume (under 1M) a simple two level SQL join will do the trick. proc sql noprint; create table want as select a.portid, a.firmid, a.myear, a.return,(b.sumreturn/b.cntreturn) as avgreturn        from        return as a,        (select portid, myear, sum(return) as sumreturn, count(return) as cntreturn                from return group by portid, myear) as b where a.portid <> b.portid and a.myear = b.myear; quit; Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more

Question as been already answered (nice and simple btw), still I'll post here my own suggestion which is aimed for performance as it was said that the original dataset was 20 millions large. To be fast I would avoid any approach which is IO intensive (DOW, index, or anything that involves re-merging). Knowing that the logic of the problem is essentially a mix of looking ahead and memorizing a set of records, this can be achieved with hashing. So, it's just a matter of reading a record ahead and storing the sequence of record in which first and last are negative. Then output the whole sequence averaging VALUE for the "middle" records. data want; set have end=_EOF;     retain _N 1 _VALUE 0;     drop _:;     if _N_ eq 1 then do;        declare Hash HT ();        HT.defineKey('_N');        HT.defineData('ID','DATE','VALUE'); * dataset variables to keep;        HT.defineDone();     end; * init hash;     _RC=HT.replace(); * store into hash;     if (_N gt 1 and VALUE le 0) or _EOF then do; * look ahead negative or end of file;        _VALUE=VALUE; * store last negative;        if HT.find(key: 1) then stop; * retrieve first negative;           _VALUE=mean(ifn(VALUE>0,.,VALUE),ifn(_VALUE>0,.,_VALUE));        if VALUE gt 0 then VALUE=_VALUE;        output; * output first in sequence;        do _I=2 to _N-1; * output looks ahead;                 if HT.find(key: _I) then stop; if HT.remove(key: _I) then stop;           if VALUE gt 0 then VALUE=_VALUE; * if positive set negative average;           output; * output sequence;        end;        if not HT.find(key: 1) then _VALUE=VALUE; else stop; * retrieve first negative;        if HT.find()then stop; if HT.remove() then stop; * retrieve last negative;        _N=1;              if _EOF then do; * end of file?;           if VALUE gt 0 then VALUE=_VALUE; * last record is positive?;           output; * output last in sequence;        end;               if HT.replace() then stop; * store as first negative for next look ahead;     end;     _N+1; * count looks ahead;       run; Positive value in the first or last record will be set the next or last negative value in sequence. Cheers from Portugal. Daniel Santos @ www.cgd.pt ... View more