sql - SAS determine first and last non-missing ID / date by class for each variable -

i want report of variables in longitudinal/panel data set city. proc means has been great this, cannot handle inclusion of columns stating first , last date variable in question nonmissing.

my data looks this:

data have; input date $ city $ var1 var2 var3;       datalines; 2006q1  nyc   .  0.1   4 2006q2  nyc   .  0.48  6 2006q3  nyc   .  0.55  7 2006q4  nyc   5  0.33  2 2007q1  nyc   8    .  10 2007q3  nyc  12    .   15 2006q1  bos  11  0.6   . 2006q2  bos   6  0.81  . 2006q3  bos   1  0.66  9 2006q4  bos   2  0.5   1 2007q1  bos   4  0.61  8 2007q3  bos  19  0.4   1 ; run; 

my desired output table looks this:

city variable  first nonmiss  last nonmiss  bos    var1   2006q1         2007q3   bos    var2   2006q1         2007q3   bos    var3   2006q3         2007q3   nyc    var1   2006q4         2007q3   nyc    var2   2006q1         2006q4  nyc    var3   2006q1         2007q3 

perhaps proc transpose create table in proc sql best way this, i'm open suggestions -- , i'm not start.

thanks!

i proc transpose approach here; it's straightforward. takes advantage of fact first. , last. defined after where clause implemented.

proc sort data=have;   city date; run;  proc transpose data=have out=have_t;   city date;   var var1-var3; run;  proc sort data=have_t;   city _name_ date; run;  data want;   set have_t;   city _name_ date;   retain first_nonmiss;   not missing(col1);   if first._name_ do;     first_nonmiss = date;   end;   if last._name_ do;     last_nonmiss = date;     output;     call missing(of first_nonmiss);  *i safety though irrelevant here - later code might make relevant;   end; run;