Yes Indexes and point are welcome. I know, I was able to do it after many-to-many merge using PROC SQL then use couple of datasteps to get the required results. But so far wasn't successful to get SET statement code work correctly, which is what I'm interested in in this discussion.  Thanks all. ... View more

So here is a mock data. Objective is that for each timepoint (in A) calculate how many visits occurred before that timepoint for each ID, so that expected outcome would be below. My question, is how to do that using SET statements and dataset (and not using merges or SQL). ID    timepoint           NumVisitsBeforTimePoint 1     1                   2 1     2                   5 1     3                   8 1     4                   9 1     5                   10 2     1                   2 2     2                   5 . . and so forth data A; input timepoint $2. refDate mmddyy10.; format refDate mmddyy10.; cards; 1 03/01/2010 2 06/01/2011 3 01/01/2012 4 07/01/2012 5 02/01/2013 ; run; %let start='01jan2010'd; data B;       format visitdate mmddyy10.;       do id = 1 to 5;             do visit=1 to 10 ;                   retain start &start;                   start= rand("Uniform") * 15 + 120 + start;                   visitdate=start;                   if visit=1 then start=&start;                   output;             end;       end;       drop start; run; ... View more

Here is a small macro (using proc transpose)....not exactly the same, but converts to one record per ID      you can add more variable to the "list=", and specify their prefixes in the "Prefixlist=" data have; input  ID $    @6 Date  yymmdd10.    @26 code   @40       code_descrip $20.; format Date  yymmdd10.; cards; 101  2011-11-16          250           Diabetes 101  2012-12-18          400           hypertension 101  2012-12-18          401           nausea 101  2012-12-18          402           bleeding run; %macro WideTranspose (data=, out= ,by=, id=,where= ,list=, Prefixlist=);   %let n=1;   %do %until (&var=);   %let var =%scan (&list,&n);   %let prefix= %scan (&Prefixlist,&n);   %put &var;     proc sort data=&data;      by &by;      run;   proc transpose data=&data out=out&n (drop= _:) prefix=&prefix LET;   by &by;   var &var;   %if %length (&id) %then %do;   id &id;   %end;   where &where;   run;   %let n=%eval(&n+1);   %let var =%scan (&list,&n);   %end;   %let dn= %eval(&n-1);   %put &dn;   data &out;   merge out1-out&dn;   by &by;   run; %mend; %WideTranspose (data=have,   out=want ,   by=id,   id= ,   where= ,   list=date code   code_descrip,   Prefixlist=date code   code_descrip); ... View more