Summarizing or Expanding Data into Weeks

Occasional Contributor
Posts: 6

Summarizing or Expanding Data into Weeks

Good Morning,

I have the following data below (first 10 observations).

Enrolled Frequency

 06/02/2014 1 10/09/2014 2 10/16/2014 4 10/17/2014 3 10/18/2014 1 10/20/2014 5 10/21/2014 2 10/22/2014 1 10/27/2014 4 10/28/2014 1

I need to convert the total frequency into weeks with cumulative summary of enrolled:

Week         Enrolled

6/2/2014    1

6/9/2014    1

...               ...

10/6/2014  1

10/13/2014 3

10/20/2014 16

10/27/2014  23

Super User
Posts: 23,663

Re: Summarizing or Expanding Data into Weeks

I'm not following how you're doing the translation between days/weeks. Can you please expand your example.

Occasional Contributor
Posts: 6

Re: Summarizing or Expanding Data into Weeks

I need to convert the frequency into a cumulative frequency by weeks of the year.

Occasional Contributor
Posts: 6

Re: Summarizing or Expanding Data into Weeks

I still need to collapse this into weeks though to get the second part of my question.
Super User
Posts: 23,663

Re: Summarizing or Expanding Data into Weeks

I think Proc Expand can do what you want, do you have SAS/ETS licensed?

https://communities.sas.com/t5/SAS-Procedures/PROC-EXPAND-daily-to-weekly-aggregation-with-missing-v...

Contributor
Posts: 38

Re: Summarizing or Expanding Data into Weeks

[ Edited ]
``````data have;
format date week mmddyy10.;
input date mmddyy10. enroll;
week = intnx('week',date,0,'begin')+1;
cards;
06/02/2014	1
10/09/2014	2
10/16/2014	4
10/17/2014	3
10/18/2014	1
10/20/2014	5
10/21/2014	2
10/22/2014	1
10/27/2014	4
10/28/2014	1
;
run;
data want(drop=i rc date enroll);
if _n_ = 1 then do;
if 0 then set have;
declare hash h (dataset:'have',multidata&colon;'y');
h.definekey('week');
h.definedata('enroll');
h.definedone();
end;
call missing (of _all_);
do date = '01jan2014'd to '31dec2014'd by 7;
week = intnx('week',date,0,'begin')+1;
rc=h.find();
do i = 1 to 10 while (rc=0);
enroll_tot + enroll;
rc=h.find_next();
end;
output;
end;
run;``````
Discussion stats
• 5 replies
• 252 views
• 0 likes
• 3 in conversation