Monthly Summary Report based on start and end dates?

[SASbeginnerEPI]

I have a data set like this:

Location       Setting_Type        Start_Date        End_Date                 n    

A                   School                 08Aug2020       02Dec2020              50

B                   Daycare               20Sep2020       12Jan2021              25

C                   School                 13July2020       30Aug2020             10

D                   School                 01Nov2020       018Dec2020           30

E                   Daycare               06Oct2020       05Feb2021              10

I want to a report like this:

Location                          Jul20      Aug20      Sep20      Oct20        Nov20     Dec20      Jan21     Feb21      n  

School   total active           1            2               1              1                 2             2                                        80

               new                      1            1                                                  1

               closed                                1                                                                  2

Daycare total active                                         1              2                 2                2              2             1       35

               new                                                    1              1                 

               closed                                                                                                                     1             1

Any ideas? I'm very new to SAS, so I'm still learning what reporting options exist. I would appreciate even if someone could point me in the right direction. Thanks in advance!

[ballardw]

So how do we tell what is "active", new or closed?

What does N represent in the want?

Do you want a report that looks like the "want" (this is something people read) or a data set for further manipulation?

[SASbeginnerEPI]

Active refers to any site that was open during that month (open is between start date and end date), new would be a site that had a start date during that month, and closed would be a site that had an end date during that month. 'n' is the number of participants for the total program (not date based).

This is an example data-set, but the real one is much larger. The 'want' report is something that people would read, and would not require further manipulation. I've already done other data cleaning and manipulation for this data set in SAS, so I was hoping to be able to produce the finished product there too. Unfortunately, I'm having trouble finding a proc that allows for a table with so many computed rows... Honestly I'm still learning what SAS is capable of doing, and what I should continue to do in excel.

[PaigeMiller]

Please explain how N = 80 in the output. How is this calculated from the original data?

[SASbeginnerEPI]

Oops should be n=90. Thanks for catching that!

[PaigeMiller · Accepted solution]

Pretty close, I don't have a row showing closed, but otherwise this is what you asked for. I'm sure adding closed would be simple but I have spent enough time on this. 

The idea is to re-arrange your data into a form where PROC REPORT can work with. This is a "long" data set, one row for each month rather than a start_date and end_date in each row.

data have;
input Location   $    Setting_Type  $      Start_Date  :date9.      End_Date  :date9.               n ;
cards;
A                   School                 08Aug2020       02Dec2020              50
B                   Daycare               20Sep2020       12Jan2021              25
C                   School                13Jul2020       30Aug2020             10
D                   School                 01Nov2020       018Dec2020           30
E                   Daycare               06Oct2020       05Feb2021              10
;
data intermediate;
    set have;
    month=start_date;
    incr=0; 
    do while (month<intnx('month',end_date,0,'b'));
        condition='Active'; 
        if incr=0 then nn=n; else nn=0;
        month=intnx('month',start_date,incr,'b');
        output;
        nn=.;
        if incr=0 then do; condition='New'; output; end;
        incr=incr+1;
    end;
    drop incr;
run;

proc report data=intermediate;
    columns setting_type condition month,n nn;
    define setting_type /group 'Location' order=data;
    define condition /group ' ';
    define month/across format=monyy7. order=internal;
    define n/analysis n ' ';
    define nn/analysis sum "N";
run;

        

[SASbeginnerEPI]

Thank you so much!