topic Getting the last 12 months data in SAS Programming

Getting the last 12 months data

VJB — Fri, 27 Sep 2013 02:19:05 GMT

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.

Re: Getting the last 12 months data

PGStats — Fri, 27 Sep 2013 03:06:23 GMT

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;

Re: Getting the last 12 months data

Haikuo — Fri, 27 Sep 2013 03:49:18 GMT

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

Re: Getting the last 12 months data

VJB — Fri, 27 Sep 2013 15:00:05 GMT

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

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

Re: Getting the last 12 months data

Haikuo — Fri, 27 Sep 2013 15:10:46 GMT

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;

Re: Getting the last 12 months data

VJB — Fri, 27 Sep 2013 19:47:01 GMT

Thank you Hai. It worked ...

Re: Getting the last 12 months data

Fabeeha — Thu, 16 May 2019 11:16:02 GMT

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;