Comparing observations for Parallel Periods

[Mj296]

I hope that somebody could help, as I am fairly new to SAS coding and I am sure this is perhaps really simple. I am looking to show the number of applications received and those rejected between a start date of 1August 2017 and Today’s date (up to 31st July 2018). The date period needs to then be reset (ideally automatically rather than resetting a macro variable) at 1August 2018 for the next cycle i.e. up to 31st July 2019. Additionally, I need to show a daily comparison of current applications compared to those in the previous year. Any pointers would be great!

The dataset I have is structured like this

Stage                     Date

REJ	22Apr2018
APP	01Jun2017
APP	03Jun2017
APP	09Jan2018
REJ	18Apr2017
APP	23Mar2018
APP	20Apr2017
REJ	20Apr2018
APP	05Dec2017
APP	12Mar2018
REJ	17Dec2017
APP	12Aug2017
APP	05Dec2017
REJ	12Mar2018
REJ	17Dec2017
APP	19Nov2017
APP	23Mar2018
REJ	29Nov2017

[RW9]

Just create a flag for year:

data want;
  set have;
  do i=2000 to 2040;
    if mdy(8,1,i) <= date <= mdy(8,31,i+1) then active_year=i;
  end;
run;

You then have active_year which you can do summaries and such like on.  Note not tested, post test data in the form of a datastep in future.

[novinosrin]

Can you please post a sample of your expected output?

[ballardw]

---

@Mj296 wrote:

I hope that somebody could help, as I am fairly new to SAS coding and I am sure this is perhaps really simple. I am looking to show the number of applications received and those rejected between a start date of 1August 2017 and Today’s date (up to 31st July 2018). The date period needs to then be reset (ideally automatically rather than resetting a macro variable) at 1August 2018 for the next cycle i.e. up to 31st July 2019. Additionally, I need to show a daily comparison of current applications compared to those in the previous year. Any pointers would be great!

---

For some clarification:

Are the dates of interest for comparison always 1 August to 31 July of the following year?

By automatically reset you mean 1) examine the date the program runs and select dates between 1 August (of the current year if between Aug and December, previous year if the current data is 1 Jan ) and 31 July (of the following year if the date the program runs is in August to December or of current year if the date is 1 January or later?

By "I need to show a daily comparison of current applications" do you mean compare 1 August 2016 with 1 August 2017? What kind of comparison do you want to do for dates with no records? Do you collect records on Sundays? If not any record from the previous year whose date is a Sunday in the current period won't have anything to compare with. So you could potentially have 52 days from the previous year with no comparison possible (kind of a high rate).

I would likely start by creating a separate variable that would be the reportdatayear or some such.

In a data step this line:

   reportdatayear = year(date) + (Month(date) ge 8);

Creates a yeargroup regardless of the time the program is run. But would allow selecting by its value. For instance: if reportdatayear=2018 would get the 1 August 2017 to 31 July 2018.

For comparison you could select any two years (need not actually be consecutive) with: if reportdatayear in (2017 2018). Use the reportdatayear as a class, classification or similar grouping variable for any analysis.