BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MikeFranz
Quartz | Level 8
Thanks for the response, I'll follow up with support.
Reeza
Super User

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

@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 Smiley Wink

 

 

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
Quartz | Level 8
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
Rhodochrosite | Level 12

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
Quartz | Level 8
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
Quartz | Level 8

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
Rhodochrosite | Level 12
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
Quartz | Level 8
Many thanks, reading through it now 🙂
Reeza
Super User

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
Quartz | Level 8
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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 1919 views
  • 0 likes
  • 5 in conversation