DATA Step, Macro, Functions and more

Retaining values by a condition

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Retaining values by a condition

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

 


Accepted Solutions
Solution
‎10-19-2017 10:24 AM
Super User
Posts: 10,571

Re: Retaining values by a condition

Posted in reply to Adnan_Razaq

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: Retaining values by a condition

Posted in reply to Adnan_Razaq

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.

 

 

 

Contributor
Posts: 26

Re: Retaining values by a condition

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

Super User
Super User
Posts: 9,840

Re: Retaining values by a condition

Posted in reply to Adnan_Razaq

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.

Solution
‎10-19-2017 10:24 AM
Super User
Posts: 10,571

Re: Retaining values by a condition

Posted in reply to Adnan_Razaq

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 148 views
  • 0 likes
  • 3 in conversation