question about count variable - resetting

Reply
Occasional Contributor
Posts: 9

question about count variable - resetting

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

11701
111202
112503
112804
113005
13101
13102
13203
13404
13405
13806
13807
131008
131109
1312010
1313011
1314012
1315013
1315014
1316015
1322016
1322017
1325018
146019
148020
152021
 
 

 

Super User
Posts: 17,750

Re: question about count variable - resetting

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. 

 

Occasional Contributor
Posts: 9

Re: question about count variable - resetting

Hi! Thanks -- I started with :

 

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

 

 

Super User
Posts: 17,750

Re: question about count variable - resetting

The starting data, not your code. We don't know what the input data is compared to the output.
Super User
Posts: 5,071

Re: question about count variable - resetting

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.

Ask a Question
Discussion stats
  • 4 replies
  • 60 views
  • 0 likes
  • 3 in conversation