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!
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;
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?
Please explain how N = 80 in the output. How is this calculated from the original data?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.