count how many patients available at each visit given their study duration time

[fengyuwuzu]

Patients visit at month in first 6 months and then every 3 months after that.

data have;
    input id $ time; /* time is study duration, unit is month,  */
    datalines;
        1 1.5
        2 3.4
        3 5.6
        4 9.8
        5 13.2
        6 2.5
        7 10.1
        8 15.6
        9 7.5
        10 21.1;
RUN;

/* each cycle is a month; want to know how many patients at each cycle */

What I want is like below:

    cycle1  10 /* 10 patients alive at end of 1st month */
    cycle2  9  /* 9 alive at end of 2nd month */
    cycle3  8
    cycle4  7
    cycle5  7
    cycle6  6
    cycle9  5 /* after first 6 months, patients visit every 3 months */
    cycle12 3
    cycle15 2
    cycle18 1
    cycle21 1
    cycle24 0 /* if there is time >27, then cycle27 has value 0 */

My current idea is using select in proc sql:

proc sql;
    create table want as
    select count(id) into: cycle1 from have where time>=1;
    select count(id) into: cycle2 from have where time>=2;
... 
    select count(id) into: cycle6 from have where time>=6;
    select count(id) into: cycle9 from have where time>=9;
    select count(id) into: cycle12 from have where time>=12;
    ...
QUIT;

But I need to determine the last cycle. Maybe something like: last cycle = int(max(time)/3)*3

[koyelghosh]

Honestly speaking, I am not clear with what is the relationship between have and want datasets in this case. So not sure if this one will fit your bill. This code creates how many patients survived at the start/end of the month(depending upon the rounding off). Please let me know if this is what you wanted.

data have;
    INPUT id $ time; /* time is study duration, unit is month,  */
    DATALINES;
        1 1.5
        2 3.4
        3 5.6
        4 9.8
        5 13.2
        6 2.5
        7 10.1
        8 15.6
        9 7.5
        10 21.1;
RUN;

DATA Processed;
	SET have;
	Month = ROUND(time);
RUN;

PROC SQL;
	SELECT COUNT(*) INTO :TotalRows FROM Processed ;
QUIT;

PROC REPORT DATA=Processed;
	COLUMNS Month Month=Survived TotalSurvived;
	DEFINE MONTH / GROUP;
	DEFINE Survived / ANALYSIS N NOPRINT;
	DEFINE TotalSurvived / COMPUTED;
	COMPUTE BEFORE;
		TotalPresent = &TotalRows;
	ENDCOMP;
	COMPUTE TotalSurvived;
		TotalPresent = TotalPresent - _C2_;
		TotalSurvived = TotalPresent;
	ENDCOMP;
RUN;

Output is as below

Output

[fengyuwuzu]

Thank you, koyelghosh, for your help. It is helpful but not exactly what I want. Sorry I did not make it clearer. 

Patients will visit every month in first 6 months, and after that every 3 months, i.e. after 6 months, I only count every 3 months, like 9, 12, 15 months etc. Based on the sample data, I want something like:

Month   count

1           10

2           9

3           8

4           7

5           7

6           6

9           5

12         3

15         2

18         1

21         1

24         0

[andreas_lds · Accepted solution]

I am sure that a shorter solution is possible, but i can't spend more time on finding it.

data ByMonth;
   set have;

   length Month 8;

   do Month = 1 to min(int(time), 6);
      output;
   end;

   if int(time) >= 9 then do;
      do Month = 9 to int(time) by 3;
         output;
      end;
   end;
run;

proc summary data=ByMonth nway;
   class Month;
   output out=Counted(drop=_type_ rename=(_freq_=count));
run;

data want;
   set counted end=lastObs;

   output;

   if lastObs then do;
      Month = Month + 3;
      Count = 0;
      output;
   end;
run;