🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Carrying Over Value when missing

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Carrying Over Value when missing

``````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;``````
5 REPLIES 5
Tourmaline | Level 20

## Re: Carrying Over Value when missing

``````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;``````
Tourmaline | Level 20

## 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;``````
Quartz | Level 8

## Re: Carrying Over Value when missing

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

Tourmaline | Level 20

## 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?

Opal | Level 21

## 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).

Discussion stats
• 5 replies
• 719 views
• 0 likes
• 3 in conversation