Retaining values by a condition

[Adnan_Razaq]

Hi All,

I have dataset which is updated on a monthly basis and certain values can change on update.

I have the following dataset

　

data have;

input account_id reporting_date sales_date sale_amount admin_cost fines interest ;

　

INFORMAT reporting_date sales_date date9.;

format reporting_date sales_date date9.;

cards;

1000 31JAN2017 . . . . .

1000 28FEB2017 . . . . .

1000 31MAR2017 06MAR2017 100000 1000 500 200

1000 30APR2017 06MAR2017 100000 1500 600 220

1000 31MAY2017 06MAR2017 100000 1100 200 300

2000 31JAN2017 . . . . .

2000 28FEB2017 24FEB2017 80000 50 200 365

2000 31MAR2017 24FEB2017 80000 98 25 36

2000 30APR2017 24FEB2017 80000 150 65 96

;

RUN;

What I want to do is to retain the values of variables admin_cost fines and interest once the sale date is first populated.

Many Thanks

Adnan

[RW9]

How do you mean retain, the example you give doesn't have any records after the sales date is populated.  Do you mean the records before that event?  If so then process is the same, you just sort the data in reverse, so this:

1000 31JAN2017 . . . . .

1000 28FEB2017 . . . . .

1000 31MAR2017 06MAR2017 100000 1000 500 200 

1000 30APR2017 06MAR2017 100000 1500 600 220

Becomes

1000 30APR2017 06MAR2017 100000 1500 600 220

1000 31MAR2017 06MAR2017 100000 1000 500 200

1000 28FEB2017 . . . . .

1000 31JAN2017 . . . . .

Then if fist group set retains to missing, then if sales_date not null then retains = data, if missing then use retained variables instead.

[Adnan_Razaq]

What I need is

1000 31JAN2017 . . . . .

1000 28FEB2017 . . . . .

1000 31MAR2017 06MAR2017 100000 1000 500 200

1000 30APR2017 06MAR2017 100000 1000 500 200

1000 31MAY2017 06MAR2017 100000 1000 500 200

I want to retain to the values in bold for the reporting months after the sales date is populated

[RW9]

Its already there in the test data?  If thats an error on your part then its still the same thing.  Reverse sort the data so that the row you want to retain is above the row you want to put the data into.  Then retain at the point you want (or you could just retain each rows values), and then replace when missing.

[Kurt_Bremser · Accepted solution]

First of all: Please, and I mean PLEASE! use either the {i} or "little running man" icons (6th and 7th above the posting window) for code.

Your example data code looks like this after pasting into my Enterprise Guide:

data have;

 

input account_id reporting_date sales_date sale_amount admin_cost fines interest ;

 

　

INFORMAT reporting_date sales_date date9.;

format reporting_date sales_date date9.;

 

cards;

 

1000 31JAN2017 . . . . .

 

1000 28FEB2017 . . . . .

 

1000 31MAR2017 06MAR2017 100000 1000 500 200

 

1000 30APR2017 06MAR2017 100000 1500 600 220

 

1000 31MAY2017 06MAR2017 100000 1100 200 300

 

2000 31JAN2017 . . . . .

 

2000 28FEB2017 24FEB2017 80000 50 200 365

 

2000 31MAR2017 24FEB2017 80000 98 25 36

 

2000 30APR2017 24FEB2017 80000 150 65 96

 

;

 

RUN;

and produces an error when submitted.

Unusable garbage.

After going over it, this is what it should look like:

data have;
input account_id reporting_date sales_date sale_amount admin_cost fines interest;
informat reporting_date sales_date date9.;
format reporting_date sales_date date9.;
cards;
1000 31JAN2017 . . . . .
1000 28FEB2017 . . . . .
1000 31MAR2017 06MAR2017 100000 1000 500 200
1000 30APR2017 06MAR2017 100000 1500 600 220
1000 31MAY2017 06MAR2017 100000 1100 200 300
2000 31JAN2017 . . . . .
2000 28FEB2017 24FEB2017 80000 50 200 365
2000 31MAR2017 24FEB2017 80000 98 25 36
2000 30APR2017 24FEB2017 80000 150 65 96
;
run;

Given your subsequent post, I think this is what you want:

data want;
set have;
by account_id;
retain k_admin_cost k_fines k_interest;
if first.account_id
then do;
  k_admin_cost = .;
  k_fines = .;
  k_interest = .;
end;
if k_admin_cost ne .
then admin_cost = k_admin_cost;
else if admin_cost ne .
then k_admin_cost = admin_cost;
if k_fines ne .
then fines = k_fines;
else if fines ne .
then k_fines = fines;
if k_interest ne .
then interest = k_interest;
else if interest ne .
then k_interest = interest;
drop k_admin_cost k_fines k_interest;
run;