DATA Step, Macro, Functions and more

replace with last observation data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

replace with last observation data

Hello, I would like to replace value in the amount with last observation amount value when only the description is "r" or "ra" within same account. Could you please help me how to do that ? Thank you so much !

data have;

infile datalines delimiter=',';

input account description $ amount;

datalines;

123,a,0

123,a,0

123,r,1    /**<- replace with last value (30) when "r" or "ra" */

123,r,20   /**<- replace with last value (30) when "r" or "ra" */

123,ra,30

123,w,4

234,b,1

234,c,20

234,ra,400  /**<- replace with last value (200) when "r" or "ra" */

234,r,200

234,c,200

;

run;

data want;

infile datalines delimiter=',';

input account description $ amount;

datalines;

123,a,0

123,a,0

123,r,30

123,r,30

123,ra,30

123,w,4

234,b,1

234,c,20

234,ra,200

234,r,200

234,c,200

;

run;


Accepted Solutions
Solution
‎03-24-2014 04:40 AM
Frequent Contributor
Posts: 106

Re: replace with last observation data

data have;

infile datalines delimiter=',';

input account description $ amount;

datalines;

123,a,0

123,a,0

123,r,1

123,r,20

123,ra,30

123,w,4

234,b,1

234,c,20

234,ra,400

234,r,200

234,c,200

;

run;

data test;

set have(where =(description in ('r','ra')));

by account;if last.account then output ;run;

proc sql;

update have 

set amount=(select amount from test where test.account=have.account )

where have.description in ('r','ra');

quit;

View solution in original post


All Replies
Frequent Contributor
Posts: 75

Re: replace with last observation data

there is huge diff in input data and ouput data. pls check and pos your requirement again.

for eg 123,r,30 and 234,r,300 is not avilable in have data set.

Occasional Contributor
Posts: 17

Re: replace with last observation data

HI TarunKumar,

I have updated my codes for your review. Thanks !

Solution
‎03-24-2014 04:40 AM
Frequent Contributor
Posts: 106

Re: replace with last observation data

data have;

infile datalines delimiter=',';

input account description $ amount;

datalines;

123,a,0

123,a,0

123,r,1

123,r,20

123,ra,30

123,w,4

234,b,1

234,c,20

234,ra,400

234,r,200

234,c,200

;

run;

data test;

set have(where =(description in ('r','ra')));

by account;if last.account then output ;run;

proc sql;

update have 

set amount=(select amount from test where test.account=have.account )

where have.description in ('r','ra');

quit;

Occasional Contributor
Posts: 17

Re: replace with last observation data

Thank you so much pradeepalankar !

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 263 views
  • 1 like
  • 3 in conversation