question about count variable - resetting

[gonzy31]

I am trying to create a count variable to count the number of patient visits without insurance for each different physician. However, my count variable is not reliably resetting with each new physician (phycode) -- sometimes is and sometimes is not.  Is there a way to fix this?  

Here is my code:

proc sort data=IN.AggregateDoc_0514;
by phycode;
run;
Data medicaidpts;
Set IN.AggregateDoc_0514;
keep phycode patcode count insurance;
if insurance=0;
by phycode;
retain count;
count+1;
if first.phycode then count=1;
Run;

Here is a bit of the output I keep getting:

PHYCODE PATCODE insurance count

11	7	0	1
11	12	0	2
11	25	0	3
11	28	0	4
11	30	0	5
13	1	0	1
13	1	0	2
13	2	0	3
13	4	0	4
13	4	0	5
13	8	0	6
13	8	0	7
13	10	0	8
13	11	0	9
13	12	0	10
13	13	0	11
13	14	0	12
13	15	0	13
13	15	0	14
13	16	0	15
13	22	0	16
13	22	0	17
13	25	0	18
14	6	0	19
14	8	0	20
15	2	0	21

[Reeza]

You don't show what you start with.

You may be running into an order of operations issue, ie you're counting before resetting or filtering before resetting. 

[gonzy31]

Hi! Thanks -- I started with :

proc sort data=IN.AggregateDoc_0514;
by phycode;
run;

[Reeza]

The starting data, not your code. We don't know what the input data is compared to the output.

[Astounding]

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.