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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.