Find the first date with having non-missing data for each group

[hhchenfx]

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

[ballardw]

What do you expect the output to look like?

Is "date" ever missing? If so, what then?

[pink_poodle]

proc sql;
create table want as
  select * 
  from have
  where not missing(v1)
  group by id, date
  having min(date)
  order by id
;
quit;

[Tom · Accepted solution]

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;

[hhchenfx]

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

[mkeintz]

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