DATA Step, Macro, Functions and more

Change the Variable Value Depending on Its other Value

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Change the Variable Value Depending on Its other Value

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

Desired.png

 

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

 

Thanks


Accepted Solutions
Solution
‎02-02-2017 06:07 PM
Super User
Posts: 17,896

Re: Change the Variable Value Depending on Its other Value

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;

View solution in original post


All Replies
Super User
Posts: 17,896

Re: Change the Variable Value Depending on Its other Value

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

Contributor
Posts: 51

Re: Change the Variable Value Depending on Its other Value

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

Solution
‎02-02-2017 06:07 PM
Super User
Posts: 17,896

Re: Change the Variable Value Depending on Its other Value

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 103 views
  • 0 likes
  • 2 in conversation