How can I count the number of active subscriptions per quarter in SAS?

Question

I am working with subscription data for a magazine, and have compiled a dataset that includes the start and end dates of each customers' subscription. The dataset includes a unique ID, a First_Date column indicating the day their subscription started, and a Last_Date column indicating the day their subscription ended.

The task is to compile counts of customers with active subscriptions for each quarter from Q1 2000 to Q2015. I have put together a dataset that has two variables: Quarter_Start (the first day of each quarter) and Quarter_End (the last day of each quarter). I would now like to add a third column to this dataset that includes the counts of active subscriptions. I have been trying to use conditional count statements in proc sql to no avail.

Here is the (faulty) code that I have been working on:

proc sql;
    create table Want as
             select a.Quarter_Start,
                    a.Quarter_End,
                    count(b.First_Date <= a.Quarter_Start &
                          b.Last_Date  >= a.Quarter_end) as Customer_Count
       from Int.Quarterly_Count as a, Int.Subscription_Dates as b;
quit;

Thanks in advance!

Answer 1

Use SUM() instead of count and add GROUP BY.
This should count customers active for the full quarter.

proc sql;
  create table want as
    select a.Quarter_Start
         , a.Quarter_End
         , sum(b.First_Date <= a.Quarter_Start 
           and b.Last_Date  >= a.Quarter_end) as Customer_Count
    from Int.Quarterly_Count as a
       , Int.Subscription_Dates as b
    group by 1,2
  ;
quit;