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

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

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

or

 

proc sort data=have out=_have;
by id;
run;
data want;
update _have(obs=0) _have;
by id ;
output;
run;
altijani
Quartz | Level 8

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

 

novinosrin
Tourmaline | Level 20

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?

Astounding
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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