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.
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
@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 |
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
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
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: