Solved: Re: Aggregating dataset by date, including missing dates - Page 2

MikeFranz · Posted 07-10-2017 02:30 PM

Thanks for the response, I'll follow up with support.

Reeza · Posted 07-10-2017 03:01 PM

There's no SAS/STAT license which may be the issue. Given the other modules I'm a bit shocked that SAS STAT wasn't included.

MikeZdeb · Posted 07-10-2017 02:03 PM

Hi, SPARSE does not add missing datesd to the data set ... yes/no? PROC FREQ can only count values that are present in the data. SPARSE only insures that table cells with ZERO values end up in a data set

AhmedAl_Attar · Posted 07-10-2017 05:55 PM

@MikeZdebis right,, the /SPARSE option does not fill-in all missing monthly values for every entry in the address/property group.

Here is an alternative solution that does that, without Proc Freq

data have;
length address $10 date 8 amount 8;
format date ddmmyy10.;
INPUT @1 address $10. @12 date :ddmmyy10. amount :best.;
datalines;
Property 1 31/01/2014 -770 
Property 1 31/01/2014 -550 
Property 1 31/01/2014 -20000 
Property 1 31/01/2014 -52329.6 
Property 1 31/07/2014 -660 
Property 1 31/10/2014 -1738 
Property 1 30/06/2015 -845.32 
Property 1 30/06/2015 845.32 
Property 1 30/06/2015 -845.32 
Property 1 31/12/2015 845.32 
Property 1 31/12/2015 -845.32 
Property 1 31/12/2015 845.32 
Property 2 31/01/2014 -1320 
Property 2 31/01/2014 -2895.99 
Property 2 31/01/2014 -653.32 
Property 2 31/01/2014 -40000 
Property 2 31/01/2014 -121954.8 
Property 2 30/09/2015 -3500 
Property 3 31/01/2014 -1200 
Property 3 31/01/2014 -28080 
Property 3 31/01/2014 -130483 
Property 3 30/11/2015 -885 
Property 3 31/12/2015 885
;
run;

/* Create aggregate by address,date */
proc summary data=have nway;
  class address date;
  var	amount;
  output out=have_sum(drop=_:) sum=;
run;

/* For every address, ensure there are no monthly gaps */
data want(KEEP=address date amount);

	length curr_dt curr_amt prev_dt prev_amt 8;
	retain curr_dt curr_amt prev_dt prev_amt;
	format curr_dt prev_dt ddmmyy10.;

	do until (last.address);

		SET have_sum;
		by address;

		if (first.address) then
			output; /* ensure it doesn't get left out */

		prev_dt= lag(date);
		prev_amt= lag(amount);
		*put prev_dt= prev_amt=;

		/* Find the monthly gap */
		diff= INTCK('month',prev_dt,date);
		*put diff=;

		if (diff > 0) then
		do; /* Fill-in missing months */

			/* Preserve current record values */
			curr_dt = date;
			curr_amt = amount;

			/* Output missing months */
			do i=1 to (diff-1) by 1;
			  date=intnx('month',prev_dt,i,'E');
			  amount=prev_amt;
			  OUTPUT;
			end;

			/* Don't forget current record */
			date=curr_dt;
			amount=curr_amt;
			OUTPUT;

		end; /* Fill-in missing months */
	end;
run;

Proc print data=want; run;

SAS Output

Obs	address	date	amount
1	Property 1	31/01/2014	-73649.60
2	Property 1	28/02/2014	-73649.60
3	Property 1	31/03/2014	-73649.60
4	Property 1	30/04/2014	-73649.60
5	Property 1	31/05/2014	-73649.60
6	Property 1	30/06/2014	-73649.60
7	Property 1	31/07/2014	-660.00
8	Property 1	31/08/2014	-660.00
9	Property 1	30/09/2014	-660.00
10	Property 1	31/10/2014	-1738.00
11	Property 1	30/11/2014	-1738.00
12	Property 1	31/12/2014	-1738.00
13	Property 1	31/01/2015	-1738.00
14	Property 1	28/02/2015	-1738.00
15	Property 1	31/03/2015	-1738.00
16	Property 1	30/04/2015	-1738.00
17	Property 1	31/05/2015	-1738.00
18	Property 1	30/06/2015	-845.32
19	Property 1	31/07/2015	-845.32
20	Property 1	31/08/2015	-845.32
21	Property 1	30/09/2015	-845.32
22	Property 1	31/10/2015	-845.32
23	Property 1	30/11/2015	-845.32
24	Property 1	31/12/2015	845.32
25	Property 2	31/01/2014	-166824.11
26	Property 2	28/02/2014	-166824.11
27	Property 2	31/03/2014	-166824.11
28	Property 2	30/04/2014	-166824.11
29	Property 2	31/05/2014	-166824.11
30	Property 2	30/06/2014	-166824.11
31	Property 2	31/07/2014	-166824.11
32	Property 2	31/08/2014	-166824.11
33	Property 2	30/09/2014	-166824.11
34	Property 2	31/10/2014	-166824.11
35	Property 2	30/11/2014	-166824.11
36	Property 2	31/12/2014	-166824.11
37	Property 2	31/01/2015	-166824.11
38	Property 2	28/02/2015	-166824.11
39	Property 2	31/03/2015	-166824.11
40	Property 2	30/04/2015	-166824.11
41	Property 2	31/05/2015	-166824.11
42	Property 2	30/06/2015	-166824.11
43	Property 2	31/07/2015	-166824.11
44	Property 2	31/08/2015	-166824.11
45	Property 2	30/09/2015	-3500.00
46	Property 3	31/01/2014	-159763.00
47	Property 3	28/02/2014	-159763.00
48	Property 3	31/03/2014	-159763.00
49	Property 3	30/04/2014	-159763.00
50	Property 3	31/05/2014	-159763.00
51	Property 3	30/06/2014	-159763.00
52	Property 3	31/07/2014	-159763.00
53	Property 3	31/08/2014	-159763.00
54	Property 3	30/09/2014	-159763.00
55	Property 3	31/10/2014	-159763.00
56	Property 3	30/11/2014	-159763.00
57	Property 3	31/12/2014	-159763.00
58	Property 3	31/01/2015	-159763.00
59	Property 3	28/02/2015	-159763.00
60	Property 3	31/03/2015	-159763.00
61	Property 3	30/04/2015	-159763.00
62	Property 3	31/05/2015	-159763.00
63	Property 3	30/06/2015	-159763.00
64	Property 3	31/07/2015	-159763.00
65	Property 3	31/08/2015	-159763.00
66	Property 3	30/09/2015	-159763.00
67	Property 3	31/10/2015	-159763.00
68	Property 3	30/11/2015	-885.00
69	Property 3	31/12/2015	885.00

MikeFranz · Posted 07-19-2017 12:06 PM

Oh hey, sorry, I hadn't seen this, hiding on the second page. It looks close, but it looks as though the running total is resetting every now at then? For example property 1 resets on line 7

AhmedAl_Attar · Posted 07-19-2017 01:03 PM

Hi,

This newly modified code, has a running total (value), as well as, original monthy total

data have;
length address $10 date 8 amount 8;
format date ddmmyy10.;
INPUT @1 address $10. @12 date :ddmmyy10. amount :best.;
datalines;
Property 1 31/01/2014 -770 
Property 1 31/01/2014 -550 
Property 1 31/01/2014 -20000 
Property 1 31/01/2014 -52329.6 
Property 1 31/07/2014 -660 
Property 1 31/10/2014 -1738 
Property 1 30/06/2015 -845.32 
Property 1 30/06/2015 845.32 
Property 1 30/06/2015 -845.32 
Property 1 31/12/2015 845.32 
Property 1 31/12/2015 -845.32 
Property 1 31/12/2015 845.32 
Property 2 31/01/2014 -1320 
Property 2 31/01/2014 -2895.99 
Property 2 31/01/2014 -653.32 
Property 2 31/01/2014 -40000 
Property 2 31/01/2014 -121954.8 
Property 2 30/09/2015 -3500 
Property 3 31/01/2014 -1200 
Property 3 31/01/2014 -28080 
Property 3 31/01/2014 -130483 
Property 3 30/11/2015 -885 
Property 3 31/12/2015 885
;
run;

/* Create aggregate by address,date */
proc summary data=have nway;
  class address date;
  var	amount;
  output out=have_sum(drop=_:) sum=;
run;

/* For every address, ensure there are no monthly gaps */
data want(KEEP=address date amount value);

	length curr_dt curr_amt curr_val prev_dt prev_amt value prev_val 8;
	retain curr_dt curr_amt curr_val prev_dt prev_amt value prev_val;
	format curr_dt prev_dt ddmmyy10.;

	do until (last.address);

		SET have_sum;
		by address date;

		if (first.address) then
		do;
			value = amount;
			output; /* ensure it doesn't get left out */
		end;
		else if (first.date) then
			value + amount;

		prev_dt= lag(date);
		prev_val= lag(value);
		prev_amt= lag(amount);
		put prev_dt= prev_val= prev_amt=;

		/* Find the monthly gap */
		diff= INTCK('month',prev_dt,date);
		*put diff=;

		if (diff > 0) then
		do; /* Fill-in missing months */

			/* Preserve current record values */
			curr_dt = date;
			curr_val = value;
			curr_amt = amount;

			/* Output missing months */
			do i=1 to (diff-1) by 1;
			  date=intnx('month',prev_dt,i,'E');
			  amount=prev_amt;
			  value=prev_val;
			  OUTPUT;
			end;

			/* Don't forget current record */
			date=curr_dt;
			amount=curr_amt;
			value=curr_val;
			OUTPUT;

		end; /* Fill-in missing months */
	end;
run;
Proc print data=want; run;

Hope if gives you what you are looking for,

Ahmed

MikeFranz · Posted 07-19-2017 01:23 PM

Hey Ahmed.

This is very close. I think perhaps I mentioned it only on another section of the query, but how would I ensure that all properties have months up to a certain date. i.e. if transactions only go up to 30Jan17, I would like to show previous amounts up to 30June17.

MikeFranz · Posted 07-20-2017 05:45 AM

Hey Ahmed.

Thanks again for your help here, this is working perfectly!

Couple of questions (I'm quite new to SAS, so don't completely understand the code here).
1) What is the "put" statement doing? I've read that it "holds the data", but I don't understand what that means? It means that it doesn't process the next row of data and allows you to output many rows before reinitialising the PDV?
2). Why is the "set" statement within the do loop?

Many thanks!

Mike

AhmedAl_Attar · Posted 07-20-2017 09:29 AM

Hi Mike,
1. The PUT statement here is for debugging. You can comment it out. It basically outputs current values to the log window.

2. This Style of reading the Data Set is called "The DOW Loop", you can learn more about it by reading this paper, and the papers referenced in it
http://support.sas.com/resources/papers/proceedings14/1619-2014.pdf

Thanks,
Ahmed

MikeFranz · Posted 07-20-2017 09:31 AM

Many thanks, reading through it now 🙂

Reeza · Posted 07-19-2017 01:29 PM

If you had SAS/ETS you could use use PROC TIMESERIES and PROC MEANS.

I still think a better method may be to create a master list of all dates you need, use that as CLASSDATA set with PROC MEANS. If you're aggregating, I'm not sure why you need the 'daily' entries anyways, but we're probably missing the context.

MikeFranz · Posted 07-19-2017 01:48 PM

I don't seem to have access to those function.

I think I was able to do it, Ahmed, using your solution.

I just included some more code (similar to your "fill in the missing dates") code to include all later months.

Many thanks!!

AhmedAl_Attar · Posted 07-19-2017 02:45 PM

Mike,

If you are interested in fully data driven approach, check these modifications


/* Create aggregate by address,date */
proc summary data=have nway;
  class address date;
  var	amount;
  output out=have_sum(drop=_:) sum=;
run;

/* Get Common Date Range accross all properties (&g_start_dt - &g_end_dt) */
proc sql noprint;
	select	MAX(min_dt) /* Get the latest reporting month accross all properties */
		  , MIN(max_dt) /* Get the earliest reporting month accross all properties */
	into	:g_start_dt,:g_end_dt
	FROM	(SELECT MIN(date) as min_dt ,MAX(date) as max_dt 
			 FROM work.have_sum GROUP BY address);
quit; 

/* For every address, ensure there are no monthly gaps */
data want(KEEP=address date amount value);

	length curr_dt curr_amt curr_val prev_dt prev_amt value prev_val 8;
	retain curr_dt curr_amt curr_val prev_dt prev_amt value prev_val;
	format curr_dt prev_dt ddmmyy10.;

	do until (last.address);

		SET have_sum (where=(date >= &g_start_dt)); /* Unified starting month */
		by address date;

		if (first.address) then
		do;
			value = amount;
			output; /* ensure it doesn't get left out */
		end;
		else if (first.date) then
			value + amount;

		prev_dt= lag(date);
		prev_val= lag(value);
		prev_amt= lag(amount);
		put prev_dt= prev_val= prev_amt=;

		/* Find the monthly gap */
		diff= INTCK('month',prev_dt,date);
		*put diff=;

		if (diff > 0) then
		do; /* Fill-in missing months */

			/* Preserve current record values */
			curr_dt = date;
			curr_val = value;
			curr_amt = amount;

			/* Output missing months */
			do i=1 to (diff-1) by 1 ;
			  date=intnx('month',prev_dt,i,'E');
			  amount=prev_amt;
			  value=prev_val;
			  if (date <= &g_end_dt) then OUTPUT;  /* Unified ending month */
			end;

			/* Don't forget current record */
			date=curr_dt;
			amount=curr_amt;
			value=curr_val;
			if (date <= &g_end_dt) then OUTPUT;  /* Unified ending month */

		end; /* Fill-in missing months */
	end;
run;

Proc print data=want; run;

Ahmed

Classroom Training Available!