topic Re: question about count variable - resetting in SAS Programming

question about count variable - resetting

gonzy31 — Wed, 26 Jul 2017 15:48:11 GMT

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	1
11	12	2
11	25	3
11	28	4
11	30	5
13	1	1
13	1	2
13	2	3
13	4	4
13	4	5
13	8	6
13	8	7
13	10	8
13	11	9
13	12	10
13	13	11
13	14	12
13	15	13
13	15	14
13	16	15
13	22	16
13	22	17
13	25	18
14	6	19
14	8	20
15	2	21

Re: question about count variable - resetting

Reeza — Wed, 26 Jul 2017 15:53:36 GMT

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.

Re: question about count variable - resetting

gonzy31 — Wed, 26 Jul 2017 15:54:37 GMT

Hi! Thanks -- I started with :

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

Re: question about count variable - resetting

Reeza — Wed, 26 Jul 2017 15:57:37 GMT

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

Re: question about count variable - resetting

Astounding — Wed, 26 Jul 2017 17:22:41 GMT

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.

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

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

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