topic Re: Summarizing or Expanding Data into Weeks in SAS Programming

Summarizing or Expanding Data into Weeks

coco1908 — Mon, 25 Jan 2016 17:47:14 GMT

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

Re: Summarizing or Expanding Data into Weeks

Reeza — Mon, 25 Jan 2016 17:49:57 GMT

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

Re: Summarizing or Expanding Data into Weeks

coco1908 — Mon, 25 Jan 2016 17:51:11 GMT

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

Re: Summarizing or Expanding Data into Weeks

coco1908 — Mon, 25 Jan 2016 17:59:19 GMT

I still need to collapse this into weeks though to get the second part of my question.

Re: Summarizing or Expanding Data into Weeks

DanZ — Mon, 25 Jan 2016 18:58:50 GMT

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;

Re: Summarizing or Expanding Data into Weeks

Reeza — Mon, 25 Jan 2016 18:14:31 GMT

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-values/td-p/212201