DATA Step, Macro, Functions and more

Getting the last 12 months data

Reply
New Contributor VJB
New Contributor
Posts: 3

Getting the last 12 months data

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.

Respected Advisor
Posts: 4,937

Re: Getting the last 12 months data

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

PG
Respected Advisor
Posts: 3,156

Re: Getting the last 12 months data

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

New Contributor VJB
New Contributor
Posts: 3

Re: Getting the last 12 months data

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/2014NC53457575

snapt_dt regionbalancev1
8/31/2013VA123457777
9/30/2013VA78908888
10/31/2013VA624509999
11/30/2013VA23454444
12/31/2013VA667893333
1/31/2014VA333322222
2/28/2014VA444421111
3/31/2014VA666632345
4/30/2014VA4444233456
5/31/2014VA222225678
6/30/2014VA33338907
7/31/2014VA44444443
8/31/2014VA33333456
9/30/2014VA012345
10/31/2014VA056789
11/30/2014VA03333
12/31/2014VA0222
1/31/2015VA0456789
2/28/2015VA08755
3/31/2015VA03332
4/30/2015VA03333
5/31/2015VA04567
6/30/2015VA02324
7/31/2015VA065657
8/31/2015VA043434
9/30/2015VA031232
10/31/2015VA0545656
11/30/2015VA0
8/31/2013NC123555
9/30/2013NC77775345
10/31/2013NC54988899
11/30/2013NC7347349389
12/31/2013NC35637459
1/31/2014NC7548843
2/28/2014NC4547775894
3/31/2014NC845774579
4/30/2014NC4548775847
5/31/2014NC754757454978
6/30/2014NC75475745794
7/31/2014NC745477457497
8/31/2014NC757854574
9/30/2014NC07575
10/31/2014NC05774
11/30/2014NC075758
12/31/2014NC08888
1/31/2015NC057745
2/28/2015NC0858959
3/31/2015NC085895
4/30/2015NC075844
5/31/2015NC04567
6/30/2015NC04
7/31/2015NC0444
8/31/2015NC04343
9/30/2015NC04343
10/31/2015NC04343
11/30/2015NC04343
Respected Advisor
Posts: 3,156

Re: Getting the last 12 months data

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;

New Contributor VJB
New Contributor
Posts: 3

Re: Getting the last 12 months data

Thank you Hai. It worked ...

Ask a Question
Discussion stats
  • 5 replies
  • 3660 views
  • 0 likes
  • 3 in conversation