topic Carrying Over Value when missing in SAS Procedures

Carrying Over Value when missing

altijani — Thu, 28 Jun 2018 15:54:41 GMT

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

Re: Carrying Over Value when missing

novinosrin — Thu, 28 Jun 2018 15:59:46 GMT

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;

Re: Carrying Over Value when missing

novinosrin — Thu, 28 Jun 2018 16:03:25 GMT

proc sort data=have out=_have;
by id;
run;
data want;
update _have(obs=0) _have;
by id ;
output;
run;

Re: Carrying Over Value when missing

altijani — Thu, 28 Jun 2018 16:05:49 GMT

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

Re: Carrying Over Value when missing

novinosrin — Thu, 28 Jun 2018 16:20:26 GMT

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?

Re: Carrying Over Value when missing

Astounding — Thu, 28 Jun 2018 16:23:34 GMT

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