BookmarkSubscribeRSS Feed
VJB
Calcite | Level 5 VJB
Calcite | Level 5

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.

6 REPLIES 6
PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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
Calcite | Level 5 VJB
Calcite | Level 5

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
Haikuo
Onyx | Level 15

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
Calcite | Level 5 VJB
Calcite | Level 5

Thank you Hai. It worked ...

Fabeeha
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 12148 views
  • 0 likes
  • 4 in conversation