Do loop to get cases by month

[Solph]

Hi I tried to identify IDs who are active in a period. The example below is active by fiscal year from April 1 of a yea and March 31 of the next year. I'm wondering if I want to do it by month, how do I turn the code?

data have; format entry_date discharge_date yymmdd10.; 
input id 1 @3 entry_date yymmdd10. @15 discharge_date yymmdd10. note $ 26-42;
datalines;
1 2017-05-11  2018-08-25 Active 2017,2018
2 2017-04-01  2017-08-02 Active 2017
3 2018-05-11  2018-07-20 Active 2018
4 2019-01-05  2019-04-29 Active 2018, 2019
5 2020-05-11  2020-05-20 Active 2020
6 2020-04-05  2020-06-02 Active 2020
;
quit;
proc print; run;
%macro active;
%do fy=2017 %to 2020;
proc sql;
create table active&fy as
	select distinct &fy as fy, id
	from have
	where entry_date<="31mar%eval(&fy+1)"d and (discharge_date>="01apr&fy"d or discharge_date=.);
%end;
%mend;
%active;
data active; set active2017-active2020; run; 
proc print; run;

Here is the results for a list of ID present or active by fiscal year.

Obs	fy	id
1	2017	1
2	2017	2
3	2018	1
4	2018	3
5	2018	4
6	2019	4
7	2020	5
8	2020	6

How do I turn the code to be by month (or fiscal month)?

Thanks in advance.

[PaigeMiller]

First, I don't understand why you are splitting HAVE into years, and then recombining the results. You can do this without a macro and without a loop to get years, and months would work similarly. (Although I'm not really sure what the output would be if you did it for months, what is a "fiscal month"?)

proc sql;
    create table active as
	select distinct id, year(intnx('month',max(entry_date,discharge_date),-3)) as fy
	from have;
quit;

[Solph]

Thanks, not quite. 

If it's to flag active cases by year, some (case #1,#4) cut across two years. Your code would only flag f or one year.

And I was looking for solution to produce similar data but by month. So say for #1, this case is active each month from May 2017 to Aug 2018, for 16 months. Hope it is clear now.

[smantha]

%let period= DAY;
	data have; format entry_date discharge_date yymmdd10.; 
    informat entry_date discharge_date yymmdd10.; 
    length period $10.;
	input @5 id   @7 entry_date   @19 discharge_date   @30 note :$32  ;
	if "&period."="YEAR" then do;
		start = year(entry_date);
		end = year(discharge_date);
		
		do _period = start to end;
		 period=put(_period,z4.);
		 put _all_;
		 output;
		end;
	end;
	else if "&period."="MONTH" then do;
		start = entry_date;
		end = discharge_date;
		do _period = start to end ;
		 period=put(_period,yymm10.);
		 put _all_;
		 output;
		 _period = intnx('month',_period,1,'beg');
		end;
	end;
	else if "&period."="DAY" then do;
		start = entry_date;
		end = discharge_date;
		do _period = start to end ;
		 period=put(_period,Juldate7.);
		 output;
		end;
	end;
	datalines;
	1 2017-05-11  2018-08-25 Active 2017,2018
	2 2017-04-01  2017-08-02 Active 2017
	3 2018-05-11  2018-07-20 Active 2018
	4 2019-01-05  2019-04-29 Active 2018, 2019
	5 2020-05-11  2020-05-20 Active 2020
	6 2020-04-05  2020-06-02 Active 2020
	;;;
	run;

	proc means data=have nway missing noprint;
	class period;
	var ID;
	output out= want count=;
	run;

[mklangley · Accepted solution]

@Solph  Try this. Does this produce the results you're expecting?

data want (keep=fiscal_month id);
    set have;
    x = intck("month", entry_date, discharge_date);
    format fiscal_month monyy7.;
    do i = 0 to x;
        fiscal_month = intnx('month', entry_date, i, 'b');
        output;
    end;
run;

proc sort data=want;
    by fiscal_month id;
run;

 Edit: corrected DO loop to start from 0

[Patrick]

@mklangley I believe @Solph asked for a row per year. Below amendments to your code should return such data.

data want (keep=fy id);
  set have;
  _stop = intck("year", entry_date, discharge_date);
  format fy year4.;

  do i = 0 to _stop;
    fy = intnx('year', entry_date, i, 'b');
    output;
  end;
run;

proc print data=want;
run;

[Solph]

mklangley, yours is similar to the one I googled and found on the net (see below), but it's good that you created var concatenating fiscal year and month.

data want;
	set have;
	date = entry_date;
	do until(date > discharge_date);
	    month = month(date);
	    output;
		date = intnx("MONTH", date, 1, "BEGINNING"); *Alignment SAME is clock 30 days, BEGINNING counts if present 1st day, END then last date;
	    end;
	format date Date9.;
run;
proc print; run;