Help using Base SAS procedures

Carrying Over Value when missing

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

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:

IDDateAmount
12345606/30/2017102.55
12345607/31/2017102.55
12345607/31/2017.
12345608/31/2017.
12345609/30/2017.
12345610/31/2017.
9542101/31/201844
9542102/28/201844
9542103/31/201844
9542103/31/2018.
9542104/30/2018.
9542105/31/2018.

 

This is what I need:

IDDateAmount
12345606/30/2017102.55
12345607/31/2017102.55
12345607/31/2017102.55
12345608/31/2017102.55
12345609/30/2017102.55
12345610/31/2017102.55
9542101/31/201844
9542102/28/201844
9542103/31/201844
9542103/31/201844
9542104/30/201844
9542105/31/201844

 

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;

View solution in original post


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

Posted in reply to novinosrin

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

Posted in reply to novinosrin

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.

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

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