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
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;
Can you assume the value of DATE is constant throughout all ClientNo?
I think, I can say. My anticipation is that way.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.