Solved
Contributor
Posts: 46

# Carrying Over Value when missing

[ Edited ]

Greetings,

I have some missing data that I need to fill with the latest available row, IF the ID and the dates are the same. My example is as follows:

This is what I have:

 ID Date Amount 123456 06/30/2017 102.55 123456 07/31/2017 102.55 123456 07/31/2017 . 123456 08/31/2017 . 123456 09/30/2017 . 123456 10/31/2017 . 95421 01/31/2018 44 95421 02/28/2018 44 95421 03/31/2018 44 95421 03/31/2018 . 95421 04/30/2018 . 95421 05/31/2018 .

This is what I need:

 ID Date Amount 123456 06/30/2017 102.55 123456 07/31/2017 102.55 123456 07/31/2017 102.55 123456 08/31/2017 102.55 123456 09/30/2017 102.55 123456 10/31/2017 102.55 95421 01/31/2018 44 95421 02/28/2018 44 95421 03/31/2018 44 95421 03/31/2018 44 95421 04/30/2018 44 95421 05/31/2018 44

Thanks

Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 1,803

## Re: Carrying Over Value when missing

[ Edited ]
``````data have;
input  ID	Date :mmddyy10.	Amount;
cards;
123456	06/30/2017	102.55
123456	07/31/2017	102.55
123456	07/31/2017	.
123456	08/31/2017	.
123456	09/30/2017	.
123456	10/31/2017	.
95421	01/31/2018	44
95421	02/28/2018	44
95421	03/31/2018	44
95421	03/31/2018	.
95421	04/30/2018	.
95421	05/31/2018	.
;

data want;
set have;
by id notsorted;
retain _a;
if first.id then call missing(_a);
if not missing(amount) then _a=amount;
else amount=_a;drop _a;
run;``````

All Replies
Solution
3 weeks ago
PROC Star
Posts: 1,803

## Re: Carrying Over Value when missing

[ Edited ]
``````data have;
input  ID	Date :mmddyy10.	Amount;
cards;
123456	06/30/2017	102.55
123456	07/31/2017	102.55
123456	07/31/2017	.
123456	08/31/2017	.
123456	09/30/2017	.
123456	10/31/2017	.
95421	01/31/2018	44
95421	02/28/2018	44
95421	03/31/2018	44
95421	03/31/2018	.
95421	04/30/2018	.
95421	05/31/2018	.
;

data want;
set have;
by id notsorted;
retain _a;
if first.id then call missing(_a);
if not missing(amount) then _a=amount;
else amount=_a;drop _a;
run;``````
PROC Star
Posts: 1,803

## Re: Carrying Over Value when missing

or

``````proc sort data=have out=_have;
by id;
run;
data want;
update _have(obs=0) _have;
by id ;
output;
run;``````
Contributor
Posts: 46

## Re: Carrying Over Value when missing

Thanks! My data has several columns, but I am just posting the relevant ones.

PROC Star
Posts: 1,803

## Re: Carrying Over Value when missing

Ok, just take the 1st code. I don't see any problem with that because we are only playing with amount column. I believe that's what your question is right? to fill in missing values for amount column right?

Super User
Posts: 6,772

## Re: Carrying Over Value when missing

That makes a big difference in choosing the right solution.

The DATA step RETAIN solution requires you to type in the specific variable names that you want to carry over.

The UPDATE doesn't even require that you know the variable names.  However, it carries over ALL variables (even the ones that you didn't mention because they are irrelevant here).

☑ This topic is solved.