Getting the last 12 months data

[VJB]

I have the below data set and i need to see that if there are balances with zero value and i need to to go back
12 months and pick the v1 value and replace with balance having a value 0.

snapt_dt  balance    v1
8/31/2013 12345    7777
9/30/2013  7890    8888
10/31/2013 62450  9999
11/30/2013 2345    4444
12/31/2013 66789  3333
1/31/2014 33332    2222
2/28/2014 44442    1111
3/31/2014 66663    2345
4/30/2014 444423  3456
5/31/2014 22222    5678
6/30/2014 3333    8907
7/31/2014 4444    4443
8/31/2014 3333    3456
9/30/2014 0        12345  ( The value of balance is 0)
10/31/2014 0        56789
11/30/2014 0      3333

for example -

for snap_dt 9/30/2014 the balance is 0, so i need to pick the last 12 month data i.e 9/30/2013 and get the v1 value( value is 888) and replace the balance for the snap_dt 9/30/2014

so finally i need to have 9/30/2014 888 12345 and this needs to be repeated for all the balances with value 0.

[PGStats]

Assuming you don't want to carry the amount for more than a single year :

proc sql;

create table tmp as select * from xl.'Sheet1$'n;

create table replace as

select *, intnx("MONTH", snapt_dt, 12, "END") as rDate format=yymmdd10.

from tmp;

update tmp

set balance = coalesce((select balance from replace where tmp.snapt_dt=rDate), 0)

where balance = 0;

quit;

PG

[Haikuo]

Hash() can also offer a handy solution:

data have;

input snapt_dt :mmddyy10.  balance v1;

format snapt_dt mmddyy10.;

cards;

8/31/2013 12345    7777

9/30/2013  7890 8888

10/31/2013 62450  9999

11/30/2013 2345    4444

12/31/2013 66789  3333

1/31/2014 33332    2222

2/28/2014 44442    1111

3/31/2014 66663    2345

4/30/2014 444423  3456

5/31/2014 22222    5678

6/30/2014 3333    8907

7/31/2014 4444    4443

8/31/2014 3333    3456

9/30/2014 0        12345 

10/31/2014 0        56789

11/30/2014 0      3333

;

data want;

if _n_=1 then do;

   if 0 then set have(drop=balance rename=v1=balance);

    declare hash h(dataset:'have (drop=balance rename=v1=balance)', ordered:'y');

      h.definekey('snapt_dt');

      h.definedata('balance');

      h.definedone();

end;

  set have;

    if balance=0 then rc=h.find(key:intnx("MONTH", snapt_dt, -12, "END"));

      drop rc;

run;

Haikuo

[VJB]

Thanks Hai,

              It worked . But i have forgotten that i have a state variable which is grouped . it works if we don't have state variable and in the below example it is working fine for VA ,but for other state(NC) i need to go back for 12 months for other state and get the balance if we have 0.

so for NC the value of 1st record should be 5345 instead of 7890.  Is there a way we can update this code with this new req .

9/30/2014

NC

5345

7575

snapt_dt	region	balance	v1
8/31/2013	VA	12345	7777
9/30/2013	VA	7890	8888
10/31/2013	VA	62450	9999
11/30/2013	VA	2345	4444
12/31/2013	VA	66789	3333
1/31/2014	VA	33332	2222
2/28/2014	VA	44442	1111
3/31/2014	VA	66663	2345
4/30/2014	VA	444423	3456
5/31/2014	VA	22222	5678
6/30/2014	VA	3333	8907
7/31/2014	VA	4444	4443
8/31/2014	VA	3333	3456
9/30/2014	VA	0	12345
10/31/2014	VA	0	56789
11/30/2014	VA	0	3333
12/31/2014	VA	0	222
1/31/2015	VA	0	456789
2/28/2015	VA	0	8755
3/31/2015	VA	0	3332
4/30/2015	VA	0	3333
5/31/2015	VA	0	4567
6/30/2015	VA	0	2324
7/31/2015	VA	0	65657
8/31/2015	VA	0	43434
9/30/2015	VA	0	31232
10/31/2015	VA	0	545656
11/30/2015	VA	0
8/31/2013	NC	123	555
9/30/2013	NC	7777	5345
10/31/2013	NC	5498	8899
11/30/2013	NC	7347	349389
12/31/2013	NC	35637	459
1/31/2014	NC	7548	843
2/28/2014	NC	45477	75894
3/31/2014	NC	8457	74579
4/30/2014	NC	45487	75847
5/31/2014	NC	75475	7454978
6/30/2014	NC	75475	745794
7/31/2014	NC	74547	7457497
8/31/2014	NC	75785	4574
9/30/2014	NC	0	7575
10/31/2014	NC	0	5774
11/30/2014	NC	0	75758
12/31/2014	NC	0	8888
1/31/2015	NC	0	57745
2/28/2015	NC	0	858959
3/31/2015	NC	0	85895
4/30/2015	NC	0	75844
5/31/2015	NC	0	4567
6/30/2015	NC	0	4
7/31/2015	NC	0	444
8/31/2015	NC	0	4343
9/30/2015	NC	0	4343
10/31/2015	NC	0	4343
11/30/2015	NC	0	4343

[Haikuo]

Very little tweak will do:

data want;

if _n_=1 then do;

  if 0 then set have(drop=balance rename=v1=balance);

  declare hash h(dataset:'have (drop=balance rename=v1=balance)', ordered:'y');

  h.definekey('snapt_dt','region');

  h.definedata('balance');

  h.definedone();

end;

  set have;

  if balance=0 then rc=h.find(key:intnx("MONTH", snapt_dt, -12, "END"),key:region);

  drop rc;

run;

[VJB]

Thank you Hai. It worked ...

[Fabeeha]

data zerobal restdata;
set have;
if balance=0 then output zerobal;else output restdata;
run;

data zerobal
(drop = balance
rename=( newdt=snapt_dt));
set zerobal;
format newdt mmddyy10.;
newdt=mdy(month(snapt_dt),day(snapt_dt),year(snapt_dt)-1);
run;

proc sql;
create table zerobal
as
select a.*,b.v1 as balance from zerobal a left join have b
on a.newdt=b.snapt_dt and a.region=b.region;
quit;

data final;
set restdata zerobal(drop=newdt);
run;