I think you would be better off using a user defined format to replace .=NA   ... View more

    data one; input name $ visitnum aval; datalines; a 1 10 a 2 20 a 3 30 a 4 40 a 5 50 b 1 10 b 3 30 b 4 40 b 5 50 c 1 10 c 2 20 ;   data want; set one; by name; _k=lag(visitnum); _k1=lag(aval); _visitnum=visitnum; _aval=aval; if not first.name and dif(visitnum)>1 then do; visitnum=_k+1; aval=_k1; output; visitnum=_visitnum; aval=_aval; output; end; else output; drop _:; run; ... View more

@SASPhile You might find this useful, call this macro to your convenience:       %macro VarExist(ds, var);     %local rc dsid;     %let dsid = %sysfunc(open(&ds));       %if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;         %let result = 1;         %put NOTE: Var &var exists in &ds;            drop &var;     %end;     %else %do;         %let result = 0;         %put NOTE: Var &var not exists in &ds;     %end;       %let rc = %sysfunc(close(&dsid));   %mend VarExist;    %VarExist(sashelp.class, name); ... View more

Can you give me a simulated example close to your real plz? ... View more

@SASPhile  How about   %let var=a b c d h i l m n; %let list1=%sysfunc(compress(&var,abc)); %let list2=%sysfunc(compress(&var,lmn)); %let list3=%sysfunc(compress(&var,hi)); %put &list1; %put &list2; %put &list3;   data want; array list1(*) &list1; array list2(*) &list2; array list3(*) &list3; run; ... View more

Hi, I have got the solution below that is close, however I still need help on dividing by 3 . Please test and let me know where I haven't understood the convert logic. I will rework and fix it properly. It should be a minor fix   data have; infile datalines expandtabs truncover; input Group $   INFORCE    Lag  Cum_Lag_Incrs   ; datalines; AA   201301     0          AA   201301     1    3.038434616     AA   201301     2    1.13597727 AA   201301     3    1.088584718     AA   201302     0          AA   201302     1    3.322640299     AA   201302     2    1.182485247     AA   201302     3    1.036479476     AA   201303     0          AA   201303     1    3.413788991     AA   201303     2    1.120095681     AA   201303     3    1.047491904     AA   201304     0          AA   201304     1    2.696971256     AA   201304     2    1.110429149     AA   201304     3    1.046630304     AA   201305     0          AA   201305     1    2.700299318     AA   201305     2    1.111799418     AA   201305     3    1.045353415     AA   201306     0                   Calculate AA   201306     1    3.156808222     Calculate AA   201306     2    1.213467751     Calculate AA   201306     3    1.152644537     Calculate AA   201307     0                   Calculate AA   201307     1    3.149903686     Calculate AA   201307     2    1.236895962     Calculate AA   201307     3    1.038549449     Calculate AA   201308     0                   Calculate AA   201308     1    3.388799982     Calculate AA   201308     2    1.17331458 Calculate AA   201308     3    1.037821673     Calculate AA   201309     0                   Calculate AA   201309     1    2.223705515     Calculate AA   201309     2    1.107696717     Calculate AA   201309     3    1.067502797     Calculate AA   201310     0                   Calculate AA   201310     1    2.598374951     Calculate AA   201310     2    1.183557366     Calculate AA   201310     3    1.068765806     Calculate AA   201311     0                   Calculate AA   201311     1    2.684124925     Calculate AA   201311     2    1.882073772     Calculate AA   201311     3    1.639452593     Calculate AB   201301     0          AB   201301     1    3.034393497     AB   201301     2    1.13446642 AB   201301     3    1.087136901     AB   201302     0          AB   201302     1    3.326727146     AB   201302     2    1.183939704     AB   201302     3    1.035100958     AB   201303     0          AB   201303     1    3.409248651     AB   201303     2    1.121473399     AB   201303     3    1.04609874 AB   201304     0          AB   201304     1    2.693384284     AB   201304     2    1.108952278     AB   201304     3    1.047917659     AB   201305     0          AB   201305     1    2.703620687     AB   201305     2    1.113166932     AB   201305     3    1.046639199     AB   201306     0                   Calculate AB   201306     1    3.160691097     Calculate AB   201306     2    1.214960317     Calculate AB   201306     3    1.15111152 Calculate AB   201307     0                   Calculate AB   201307     1    3.145714314     Calculate AB   201307     2    1.238417344     Calculate AB   201307     3    1.037168178     Calculate AB   201308     0                   Calculate AB   201308     1    3.384292878     Calculate AB   201308     2    1.171754072     Calculate AB   201308     3    1.039098194     Calculate AB   201309     0                   Calculate AB   201309     1    2.226440673     Calculate AB   201309     2    1.106223481     Calculate AB   201309     3    1.068815825     Calculate AB   201310     0                   Calculate AB   201310     1    2.601570952     Calculate AB   201310     2    1.181983235     Calculate AB   201310     3    1.070080388     Calculate AB   201311     0                   Calculate AB   201311     1    2.680555039     Calculate AB   201311     2    1.879570614     Calculate AB   201311     3    1.64146912 Calculate ;   proc sort data= have out=have1; by group lag; run;   data temp; do until(last.group); do _n_=1 by 1 until(last.lag); set have1; by  group lag; if lag=0 then do; output; leave; end; l1=lag(cum_lag_incrs); l2=lag2(cum_lag_incrs); l3=lag3(cum_lag_incrs); l4=lag4(cum_lag_incrs); l5=lag5(cum_lag_incrs); if _n_=1 then call missing(rollavg,l1,l2,l3,l4,l5); if _n_>=6 then rollavg=mean(l1,l2,l3,l4,l5); output; end; end; drop l1-l5; run;   proc sort data= temp out=want; by group inforce lag; run;      Regards, Naveen Srinivasan   PS I assumed you are taking one year data of 2013 and your sample data resembles very much of the actual. Assumptions might make me lose track but I love these kinda problems,so I am in it for your feedback and improvment. Thanks!       ... View more

Hi, I am little slow to grasp. May I ask a couple of questions plz-   1.  Can you explain the blanks in the Cum_Lag_Incrs? 2. Can you manually show an example of filling the 4 or 5 records of the calculate from the sample data or in other words, what will be the value of the calculate(roll avg) for the following records- AA 201306 0   Calculate AA 201306 1 3.156808222 Calculate AA 201306 2 1.213467751 Calculate AA 201306 3 1.152644537 Calculate ... View more