Change the Variable Value Depending on Its other Value

[ertr]

Hi,

I would like to ask a simple question to you. Let's pretend I have two variables and one of them keep records as date values or keep missing values based on CLIENTNO. I want to replace the missing values with date values into same variable.

If you can examine my sample data set as below, you can understand my question better.

Data Have;
Length CLIENTNO 8 DATE $ 20;
Infile Datalines Missover;
Input CLIENTNO DATE;
Datalines;
1 201601
1 201601
1 .
2 .
2 201602
2 .
3 201603
3 201603
3 .
4 201604
4 201604
4 201604
;
Run;

Desired

Or I can also create this desired output in new variable.

Thanks

[Reeza]

Can you assume the value of DATE is constant throughout all ClientNo?

[ertr]

I think, I can say. My anticipation is that way.

[Reeza · Accepted solution]

Here's the SQL way:

proc sql;
	create table want as
		select *, max(date) as date_filled
			from have
				group by clientNo;
quit;

And a data step way:

proc sort data=have;
	by clientno descending date;
run;

data want_data;
	set have;
	by clientNo;
	retain date_filled;

	if first.clientNo then
		date_filled=date;
run;