The 'WANT' dataset does not make much sense on it's own. You should not mix aggregate data (those counts) with detail data in the same row.
What if an ID had 30 detail records for visits over 3 months ? You would a lot more detail (30) than aggregates (3).
A more reasonable 'want' would look like
id month count
-- ----- -----
Perhaps reported in a layout such as
id startmonth visits_count_in_month_1 ...to... visits_count_in_month_<n> -- --------- --- --- --- --- --- --- --- --- --- --- 1 15JAN2016 0 0 2 0 0 1 0 0
where <n> is the number of months start to end.
Example:
data have;
input ID Startdate Enddate Visitdate;
format startdate enddate visitdate date9.;
informat startdate enddate visitdate date9.;
datalines;
1 15JAN2016 09AUG2016 10MAR2016
1 15JAN2016 09AUG2016 13MAR2016
1 15JAN2016 09AUG2016 09JUN2016
2 08MAR2016 12JUL2016 15MAR2016
2 08MAR2016 12JUL2016 25MAR2016
2 08MAR2016 12JUL2016 6JUN2016
2 08MAR2016 12JUL2016 3JUL2016
;
data want_way1;
* array using direct index element for frequency counting;
* index is the numeric sas date value for a month;
array months(0:%sysfunc(today())) _temporary_;
do until (last.id);
set have;
by id;
months(intnx('month', visitdate, 0)) + 1;
end;
startmonth =intnx('month', startdate, 0);
do index = 0 to intck('month', startdate, enddate);
month = intnx('month', startdate, index);
count = coalesce(months(month),0);
output;
end;
call missing (of months(*));
keep id index month count startmonth;
format month startmonth nldateymm.;
run;
ods html file='wide.html' style=plateau;
proc report data=want_way1;
columns id startmonth count,index;
define id / group;
define startmonth / min;
define index / ' ' across;
define count / 'Freq in month ';
run;
ods html close;
Produces
data
and report output
A second way to get the counts is to create a months data set covering each id. Aggregate a join of the dates coverage with the details data grouping by id and month.
Example:
data months_per_id;
set have;
by id;
if first.id;
do index = 0 to intck('month', startdate, enddate);
month = intnx('month', startdate, index);
output;
end;
keep id index month; format month nldateymm.;
run;
proc sql;
create table want_way2 as
select M.id, index, month, sum(intck('month', month, visitdate)=0) as count
from have join months_per_id as M
on have.id = M.id
group by M.id, M.index, M.month
;
... View more
