data have; input ID DataDate : $15. Var1; cards; 1 Apr101980 50 1 Apr111980 55 1 Apr171980 55 1 Apr191980 40 1 Apr241980 30 1 Feb81981 40 1 Feb101981 40 1 Feb181981 40 1 Feb271981 40 2 March181981 30 2 June271981 27 2 Sept81981 20 ; data temp; set have; year=substr(DataDate,length(DataDate)-3); /*in your real, date should be numeric, so you can do year=year(date)*/ run; data want; do n=1 by 1 until(last.year); set temp; by id year; end; do _n_=1 by 1 until (last.year); set temp; by id year; want=Var1*_n_/n; output; end; drop n; run; ... View more

I've figured out a solution.  It involves: 1) using the retain statement to first spread the period 1's span value to the rest of group, 2) sort based on the retained span value, 3) eliminate group with negative retained span, and then 4) retain only the first group based on retained span.  It may not be the most elegant, but fits the bill.  Thanks to all for trying. ... View more

While you could probably achieve this with hashing, my inclination is to use formats instead.  This solution will fail if Table 2 contains duplicate values for either ID1 or ID2, but then you would expect that to be a problem case.  This code assumes that ID1 and ID2 are both character, although that issue can be circumvented if necessary. First, create two formats from Table 2: data table2_revised;   set table2 end=done;   length label $ 9;   label=data2;   fmtname='$id1fmt';   start=id1;   output;   start=id2;   output;   if done;   hlo='O';   label='Not Found';   fmtname='$id1fmt';   output;   fmtname='$id2fmt';   output; run; proc sort data=table2_revised;   by fmtname; run; proc format cntlin=table2_revised; run; This gives you 2 formats to work with, $id1fmt and $id2fmt, which translate ID1 and ID2 respectively into the matching value for DATA2.  There might be a little more complication if DATA 2 is numeric ... it might need to be converted to character along these lines: label = put(data2, best9.); At any rate, once you have the formats, the hard part is over.  You can use the formats along these lines: data want;   set table1;   match_from_id1 = put(id1, $id1fmt.);   match_from_id2 = put(id2, $id2fmt.); run; Then you can play with the two new variables in any way you see fit. Good luck. ... View more

yeaforme if the run-time is important to you alternative ways exist to recognise the "end-of-data" on a line. The INFILE statement has options LENGTH= and COLUMN= which will give you access to the values SAS maintains for current line length and the pointer for position on the current line where the next INPUT will read data ( you can redirect it with INPUT @nnn,  but not in your situation here). The options LENGTH= and COLUMN= assign variable names to these data values. Adapting the prototype     infile myCSV dsd missover firstobs=2 eov=skip lrecl=156000 length = _len column = _col ; and the DO statement would become something like     do  until( _col=1 or _col >= _len ); I'm never quite sure what position will be set for _COL when you have read  the final column. So I suggest testing that the next position has been reset to the beginning of the next line as well as beyond the end of the current line. If you want to know from which physical position on the CSV record the data has been read, you could use _COL, or its value before the INPUT is executed, in the loop like    do  until( _col=1 or _col >= _len );      pos = _col ;       input amountc $ @;       if not missing(amountc) then do;          &varName = input(amountc, mmddyy12. );          output;       end;    end; If a relative column number (like excel) is more useful than the CSV file text position, assign the POS in the DO statement instead of its own statement, like    do pos=1 to _len  until( _col=1 or _col >= _len ); hope you'll give it a go. peterC ... View more

try to put your variables in scientific notation in &list3, your date variables in &list4 if you have any. data have; length f1 f2 $11 date1 date2 $10;   input id sex (a b c d e)($) f1 f2 date1 date2;   cards; 1 2 11 22 33 44 55 9.785643E13 8.785643E10 01/12/2012 2/12/2012 ; proc sql noprint; select catx(' ','input(',name,',best32.) as',name) into : list1 separated by ','   from dictionary.columns    where libname='WORK' and memname='HAVE' and type ='char' and name not in ('a','b','f1','f2','date1','date2'); select name into : list2 separated by ','  from dictionary.columns     where libname='WORK' and memname='HAVE' and type ='num' ; select catx(' ','input(',name,',e13.) as',name) into : list3 separated by ','  from dictionary.columns    where libname='WORK' and memname='HAVE' and name in ('f1','f2'); select catx(' ','input(',name,',mmddyy10.) as',name) into : list4 separated by ','  from dictionary.columns    where libname='WORK' and memname='HAVE' and name in ('date1','date2'); create table want as   select &list1,&list2,&list3,&list4,a,b from have; quit; proc contents data=want;run;proc print;run; ... View more