BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Adnan_Razaq
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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;

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