topic Re: Find the first date with having non-missing data for each group in SAS Programming

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

hhchenfx — Thu, 03 Mar 2022 15:12:30 GMT

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

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

ballardw — Thu, 03 Mar 2022 15:25:33 GMT

What do you expect the output to look like?

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

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

pink_poodle — Thu, 03 Mar 2022 15:31:58 GMT

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

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

Tom — Thu, 03 Mar 2022 15:38:33 GMT

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;

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

hhchenfx — Thu, 03 Mar 2022 15:40:29 GMT

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

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

mkeintz — Thu, 03 Mar 2022 15:40:54 GMT

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