Thank you both! That worked perfectly. ... View more

I want the average number of visits per provider.  Each provider is unique in each year -- so you can take an overall average number of visits per provider over the ten years (as long as each provider is uniquely identified by year and phycode).  Patcode, you are correct, is the same as count here --- but not later when I restrict the visits to only patients without insurance, but this is why I am creating a distinct count variable.   The last.year output worked -- I think you were right that I was overcomplicating it.   I then just got the average count from the output above --    proc means data=test; var count; run;   I think this solved the issue... thanks! ... View more

You need to add two pieces.  Let SAS know when a new physician begins, and set the count variable at that point.  It's actually not difficult:   proc sort data=IN.AggregateDoc_0514; by phycode; run; Data medicaidpts; Set IN.AggregateDoc_0514; by phycode; if first.phycode then count=1; keep phycode patcode count insurance; if insurance=0; by phycode; retain count; count+1; Run;   So you were missing the BY statement, and the reference to reset (if first.phycode  then ,...) came too late.  If the first patient for a physician had insurance, your IF statement (if insurance=0) would delete the data for that patient, and never reach the statement that resets COUNT. ... View more