DATA Step, Macro, Functions and more

Summarizing or Expanding Data into Weeks

Reply
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/20141
10/09/20142
10/16/20144
10/17/20143
10/18/20141
10/20/20145
10/21/20142
10/22/20141
10/27/20144
10/28/20141

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: 19,839

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: 19,839

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:'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;
Ask a Question
Discussion stats
  • 5 replies
  • 219 views
  • 0 likes
  • 3 in conversation