Aggregating data on specific status variables for each time the unique id is updated

[Sanflo]

Hello all,

I'm hoping to create an aggregate dataset on specific statuses that produces a rolling count of cases by the UID between each date update across a range of dates.

The main criteria is to count each UID from the date it first appears in a specified status until the day before the next date update. Any UID on their only/final update should continue to be counted in the status for each new date that appears until the next date updated. I've providing detail on how it should work for each UID in individually for status 1:

- Looking at UID for A it first enters on 01JAN19 and remains in the status until 05JAN19.The output for would count a 1 from 01JAN19 to 04JAN19 until the case moves to a different status.

- Looking at UID for B it first enters on 02JAN19 and doesn't move out of this status in the sample. The output would continually count a 1 from 02JAN19 for each date until B is updated.

- Looking at UID for C it first enters on 04JAN19 and remains in the status until 06JAN19.The output for would count a 1 from 04JAN19 to 05JAN19 until the case moves to a different status.

- This would be a combined count for all UIDs for each status by date for the final output.

I'm using SAS EG 7.1 and have included below SAS code that generates a sample of how my data is structured and what the desired output would look.

Any other questions or further information required I can provide more detail.

Many thanks in advance,

Sandy

data work.have;
	input UID $1. DATE :date9. STATUS 1.;
	format date date9.;
	infile datalines dlm=',';
	datalines;
A,01JAN2019,1
A,05JAN2019,2
A,07JAN2019,3
B,02JAN2019,1
C,04JAN2019,1
C,06JAN2019,2
C,07JAN2019,3
;
run;

data work.want;
	input DATE :date9. STATUS1 :1. STATUS2 :1. STATUS3 :1.;
	format date date9.;
	infile datalines dlm=',';
	datalines;
01JAN2019,1,0,0
02JAN2019,2,0,0
03JAN2019,2,0,0
04JAN2019,3,0,0
05JAN2019,2,1,0
06JAN2019,1,2,0
07JAN2019,1,0,2
;
run;

[ChrisNZ]

I find the question very unclear but his will get the ball rolling.

Does this do what you want?

data DATES;
  merge HAVE(rename=(DATE=CURR_DATE))
        HAVE(firstobs=2 keep=UID DATE rename=(UID=NEXT_UID DATE=NEXT_DATE));
  format DATE date9.;
  retain TOGGLE ;
  if UID ne lag(UID) then TOGGLE=0;                      %* New UID, reset in/out flag;
  TOGGLE=^TOGGLE;
  if UID=NEXT_UID then do DATE=CURR_DATE to NEXT_DATE-1; %* Fill missing dates to value in next record;
    output;
  end;
  else do DATE=CURR_DATE to today();                     %* Last record for UID, fill date till today;
    output;
  end;
  drop CURR_DATE NEXT_UID NEXT_DATE STATUS;
run;

proc sql;
  select DATE, sum(TOGGLE) as STATUS 
  from DATES
  group by DATE 
  order by DATE ;
quit;

DATE	STATUS
01JAN2019	1
02JAN2019	2
03JAN2019	2
04JAN2019	3
05JAN2019	2
06JAN2019	1
07JAN2019	3
08JAN2019	3
09JAN2019	3
10JAN2019	3
11JAN2019	3
12JAN2019	3
13JAN2019	3
14JAN2019	3
15JAN2019	3
16JAN2019	3
17JAN2019	3
18JAN2019	3