topic Re: Visit counter not resetting upon a new ID in SAS Programming

Visit counter not resetting upon a new ID

karen_e_wallace — Tue, 20 Mar 2018 18:57:26 GMT

From a large dataset, I am interested in counting the member ID and categorizing the number of visits based upon the DOS_Start (SAS Enterprise 7.1 (7.100.0.1966) (64-bit).

There are occasions where there are different claim numbers that have the same DOS.

An example in this data is that the member 0001 had multiple claims in the ER for DOS_Start of 4/11/17; however, I only want to count this as one visit to the ER for this analysis.

In this newest iteration of code, I'm able to get the member ID correct but not the visit. When the member ID is 0002, the visit counter should reset to 1; however, it is continuing to auto-increment as if it is still member ID 0001.

Thank you in advance for your help! - Karen

Current code:

PROC SORT DATA=WORK.SAMPLE OUT=WORK.SORTED;

BY MEMBER_AMISYS_NBR DOS_Start;

RUN;

DATA WANT;

SET WORK.SORTED;

BY MEMBER_AMISYS_NBR DOS_Start;

Format DOS_Start MMDDYY10.;

RETAIN MEMID VISIT 0;

IF FIRST.MEMBER_AMISYS_NBR THEN MEMID = MEMID + 1; /* autoincrement based upon new Amisys # */

IF FIRST.DOS_START THEN DO;

VISIT=VISIT+1; /* this works to increment visits based upon date */

END;

RUN;

Current Output:

MEMBER_AMISYS_NBR	CLAIM_NBR	DOS_Start	MCC1	MCC2	MEMID	VISIT
0001	12	03/15/2017	Primary Care	Primary Care	1	1
0001	12	03/15/2017	Primary Care	Primary Care	1	1
0001	84	03/16/2017	Hospital Outpatient	Outpatient- Other	1	2
0001	84	03/16/2017	Hospital Outpatient	Outpatient- Other	1	2
0001	43	03/17/2017	Hospital Outpatient	Outpatient- Other	1	3
0001	66	04/11/2017	Emergency Room	ED Other	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	22	04/11/2017	Emergency Room	Emergency Room	1	4
0001	44	04/11/2017	Emergency Room	ED Other	1	4
0002	6	08/18/2017	Primary Care	Primary Care	2	5
0002	6	08/18/2017	Primary Care	Primary Care	2	5

Re: Visit counter not resetting upon a new ID

Reeza — Tue, 20 Mar 2018 19:02:13 GMT

IF FIRST.MEMBER_AMISYS_NBR THEN do;

MEMID = MEMID + 1; /* autoincrement based upon new Amisys # */

visit=0;

end;

Re: Visit counter not resetting upon a new ID

karen_e_wallace — Tue, 20 Mar 2018 19:38:29 GMT

Thanks for the code...the MemID counter is working but the Visit variable is set to zero for all the rows.

Re: Visit counter not resetting upon a new ID

Reeza — Tue, 20 Mar 2018 19:45:54 GMT

Did you change your other VISIT line? If you didn't change it, this should have worked fine.

Please post your full new code.

Re: Visit counter not resetting upon a new ID

karen_e_wallace — Tue, 20 Mar 2018 19:50:06 GMT

DATA want;
SET WORK.SORTED;
BY MEMBER_AMISYS_NBR DOS_Start;
Format DOS_Start Admit_Dt MMDDYY10.;
RETAIN MEMID VISIT 0;
IF FIRST.MEMBER_AMISYS_NBR THEN DO; /* autoincrement based upon new Amisys # */
MEMID=MEMID+1;
VISIT=0; /* this works to increment visits based upon date */
END;
RUN;

Re: Visit counter not resetting upon a new ID

Reeza — Tue, 20 Mar 2018 19:52:49 GMT

Add back the increment for visit like I mentioned initially. You don't have to take out any code, you need to ADD some in.

Re: Visit counter not resetting upon a new ID

karen_e_wallace — Wed, 21 Mar 2018 12:53:42 GMT

Reeza,

Apologies I'm not understanding well how to re-order this code properly...please bear with me 🙂

Was it to add in two first. variables to auto-increment visit? Clearly the below isn't correct on my part.

Appreciate your help!

...

RETAIN MEMID VISIT 0;

IF FIRST.MEMBER_AMISYS_NBR THEN DO; /* autoincrement based upon new Amisys # */

MEMID=MEMID+1;

VISIT=0; /* this works to increment visits based upon date */

END;

IF FIRST.MEMBER_AMISYS_NBR THEN DO; /* autoincrement based upon new Amisys # */

MEMID=MEMID+1;

VISIT=VISIT+1; /* this works to increment visits based upon date */

END;

Re: Visit counter not resetting upon a new ID

Kurt_Bremser — Wed, 21 Mar 2018 13:26:01 GMT

Try this:

data want;
set work.sorted;
by MEMBER_AMISYS_NBR DOS_Start;
format DOS_Start MMDDYY10.;
retain MEMID VISIT 0;
if first.MEMBER_AMISYS_NBR
then do;
  MEMID + 1;
  VISIT = 0;
end;
if first.DOS_START then VISIT + 1;
run;

Re: Visit counter not resetting upon a new ID

karen_e_wallace — Wed, 21 Mar 2018 13:41:48 GMT

Thank you Kurt...I tweaked that a bit and now is working as:

...

RETAIN MEMID VISIT 0;

IF FIRST.MEMBER_AMISYS_NBR THEN DO;

MEMID + 1; /* autoincrement based upon new Amisys # - correct */

VISIT = 0;

END;

IF FIRST.DOS_START THEN DO;

VISIT=VISIT+1; /* this works to increment visits based upon date */

END;

Re: Visit counter not resetting upon a new ID

Kurt_Bremser — Wed, 21 Mar 2018 13:49:21 GMT

A single statement does not need a do/end in a then or else branch.

And you don't need to shout at the SAS interpreter, it understands lowercase perfectly well, and for us humans it's more readable 😉

Re: Visit counter not resetting upon a new ID

karen_e_wallace — Wed, 21 Mar 2018 14:01:15 GMT

Thanks...sorry didn't mean to shout