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 |
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.
Hi! Thanks -- I started with :
proc sort data=IN.AggregateDoc_0514;
by phycode;
run;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.