BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Solph
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mklangley
Lapis Lazuli | Level 10

@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

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
Solph
Pyrite | Level 9

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
Lapis Lazuli | Level 10
%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
Lapis Lazuli | Level 10

@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
Opal | Level 21

@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;

Patrick_0-1593027622072.png

 

Solph
Pyrite | Level 9

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;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3050 views
  • 1 like
  • 5 in conversation