Hi Everyone,
I try to find the first date (row) with non-missing value for each id.
So in my sample data below, id 1 first valid date is 1. and id 2 first valid date is 3.
Still haven't figure out a good way to do it.
Thanks,
HHC
data have;
input date id v1;
datalines;
1 1 30
2 1 .
3 1 20
1 2 .
2 2 .
3 2 1
4 2 6
5 2 .
;
Assuming you meant non-missing value of V1 then just use BY group processing.
data want;
set have;
where not missing(v1);
by id date;
if first.id;
run;
What do you expect the output to look like?
Is "date" ever missing? If so, what then?
proc sql;
create table want as
select *
from have
where not missing(v1)
group by id, date
having min(date)
order by id
;
quit;
Assuming you meant non-missing value of V1 then just use BY group processing.
data want;
set have;
where not missing(v1);
by id date;
if first.id;
run;
Thank you all for helping.
@Tom : I follow your suggestion for the other topic and I think I get the idea.
Will post it once it is done.
HHC
Given your data is already sorted by id/date, this is a simple task:
data have;
input date id v1;
datalines;
1 1 30
2 1 .
3 1 20
1 2 .
2 2 .
3 2 1
4 2 6
5 2 .
;
data want;
set have (where=(not missing(v1)));
by id;
if first.id;
run;
I take it that the "non-missing value" criterion refers to V1.
Although I assume data are sorted by ID/DATE, they really only have to be sorted by DATE within each ID. The ID's can be in any order. In this case you would change "BY ID;" to "BY ID NOTSORTED;".
And as to @ballardw question about missing dates - you could revise the WHERE parameter to ignore missing dates:
set have (where=(not missing(date) and not missing(v1)));
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.