Do loop and Intck to get cases point in time

[Solph]

Hi

Last week I asked a question about how to identify a list of cases present in a month between their entry date and discharge. See the code toward the end of post. I'm wondering if I can recycle the code to, now, identify a list of cases present point in time, on 5 different dates: April 1, June 30, Sept 30, Dec 31, and Mar 31. I think I can, but I can't exactly sure how to have it fixed. Any hint is appreciated.

Here is the data I have and the "want" data for fiscal year 2017-2018 (Apr-Mar) in the right columns.

*Get point in time on April 1, Jun 30, Sep 30, Dec 31, Mar 31;
data have; format entry_date discharge_date yymmdd10.; 
input id 1 @3 entry_date yymmdd10. @15 discharge_date yymmdd10. 
apr2017 jun2017 sep2017 dec2017 mar2018 apr2018 jun2018 sep2018 dec2018 mar2019
;
datalines;
1 2017-05-11  2018-08-25 0 1 1 1 1 1 1 0 0 0 
2 2017-04-01  2017-08-02 0 1 0 0 0 0 0 0 0 0
3 2018-05-11  2018-07-20 0 0 0 0 0 0 1 0 0 0
4 2016-01-05  2019-03-29 1 1 1 1 1 1 1 1 1 0 
;
quit;

Here is the code to identify a list of cases present in a month between their entry date and discharge.

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;  
data want (keep=FYMO id);
    set have;
    x = intck("month", entry_date, discharge_date);
    format FYMO yymmn6.;
    do i = 0 to x;
        FYMO = intnx('month', entry_date, i, 'BEGINNING');
        output;
    end;
run;

[ChrisNZ · Accepted solution]

No too sure what the question is.

Like this?

do D='01apr2017'd, '01jun2017'd, '01sep2017'd, '01dec2017'd, '01mar2018'd, '01apr2018'd, '01jun2018'd, '01sep2018'd, '01dec2018'd, '01mar2019'd;
  if  ENTRY_DATE <= D <= DISCHARGE_DATE then output;
end; 

[Solph]

Yes, it worked and I modified a bit to suit my needs. Thank a lot.

%macro loop;
%do year=2017 %to 2019;
	data want&year;
    set have;
	if discharge_date =. or discharge_date> "&enddt"d then discharge_date="&enddt"d;
	if  ENTRY_DATE <= "01apr&year."d <= DISCHARGE_DATE then ID01apr=1;
	if  ENTRY_DATE <= "30jun&year."d <= DISCHARGE_DATE then ID30jun=1;
	if  ENTRY_DATE <= "30sep&year."d <= DISCHARGE_DATE then ID30sep=1;
	if  ENTRY_DATE <= "31dec&year."d <= DISCHARGE_DATE then ID31dec=1;
	if  ENTRY_DATE <= "31mar%eval(&year+1)"d <= DISCHARGE_DATE then ID31mar=1;
	Fyear=&year;
	output want&year;
	run;
%end;
%mend;
%loop;
data want; set want2017-want2019; run;

[ChrisNZ]

Why several tables? And why a macro? Would something like this work?

data WANT;
  set HAVE;
  do FYEAR=2017 to 2019;
    DISCHARGE_DATE = min(DISCHARGE_DATE, "&enddt"d);
    if ENTRY_DATE <= mdy( 4, 1, FYEAR)   <= DISCHARGE_DATE then ID01APR=1;
    if ENTRY_DATE <= mdy( 6, 1, FYEAR)   <= DISCHARGE_DATE then ID30JUN=1;
    if ENTRY_DATE <= mdy( 9, 1, FYEAR)   <= DISCHARGE_DATE then ID30SEP=1;
    if ENTRY_DATE <= mdy(12, 1, FYEAR)   <= DISCHARGE_DATE then ID31DEC=1;
    if ENTRY_DATE <= mdy( 3, 1, FYEAR+1) <= DISCHARGE_DATE then ID31MAR=1;
  end;
run;

[ChrisNZ]

Even better if you want the zeros to be populated too:

ID01APR = ( ENTRY_DATE <= mdy( 4, 1, FYEAR)   <= DISCHARGE_DATE ) ;