topic Re: how to get the variable which had the minimun date of observation from data step in SAS Programming

how to get the variable which had the minimun date of observation from data step

Abimal_Zippi — Mon, 30 Sep 2019 09:09:35 GMT

Hello!

Could you please help me how i can get the variable which had the minimum date of observation from the list of variables (had more than 100 variables). The variables had either a missing or a specific date of observation. I know how to identify the min date for each ID by using min and array function but want to know the variable which had the minimum date of observation for each unique ID.One ID has only one observation, i.e not only the minimum date of observation but also the type of variable which had the minimum observation for each unique ID.

*Original Data structure;
data have;
input id var_1 var_2 var_3 var_4 var_5;
datalines;
1 10Jan2011 . 11Jan2012 . . 
2 12Feb2012 11Feb2013 . . .
3 21Mar2014 16Mar2014 18Mar2015 . .
4 . . 15Jan2016 10Feb2015 5Feb2015 
;

*wanted data structure;
 ID  Var    Min_date
 1   Var_1 10Jan2011
 2   Var_1 12Feb2012
 3   Var_2 16Mar2014
 4   var_5 5Feb2015

Thanks,

Re: how to get the variable which had the minimun date of observation from data step

Kurt_Bremser — Mon, 30 Sep 2019 09:18:37 GMT

Please test your code before posting; as posted, there are only missing values.

See this:

data have;
input id (var_1 var_2 var_3 var_4 var_5) (:date9.);
format var: date9.;
datalines;
1 10Jan2011 . 11Jan2012 . . 
2 12Feb2012 11Feb2013 . . .
3 21Mar2014 16Mar2014 18Mar2015 . .
4 . . 15Jan2016 10Feb2015 5Feb2015 
;

proc transpose data=have out=trans;
by id;
var var:;
run;

proc sort data=trans;
by id col1;
run;

data want;
set trans;
where col1 ne .;
by id;
if first.id;
rename _name_=var col1=min_date;
run;

Re: how to get the variable which had the minimun date of observation from data step

PeterClemmensen — Mon, 30 Sep 2019 09:37:46 GMT

data have;
infile datalines truncover;
input id (var_1-var_5)(:date9.);
format var_1-var_5 date9.;
datalines;
1 09Jan2011 . 11Jan2012 . . 
1 . 10Jan2011 11Jan2012 . . 
2 12Feb2012 11Feb2013 . . .
3 21Mar2014 16Mar2014 18Mar2015 .
4 . . 15Jan2016 10Feb2015 5Feb2015
;

data want(keep=id varname min_date);
    do until (last.id);
        set have;
        by id;
        array _ var_1-var_5;
        Min_date=min(Min_date, min(of var_1-var_5));
        var=max(var, whichn(Min_date, of var_1-var_5));
        varname=vname(_[var]);
    end;

    format min_date date9.;
run;

Re: how to get the variable which had the minimun date of observation from data step

Abimal_Zippi — Mon, 30 Sep 2019 09:42:10 GMT

That's great. Thanks a lot!.
One more question, the name of the variables are different, not like var_1, var_2.....can you advise me how can I use the transpose if the var names are different. Since I had more than 100, each with a different name.

Re: how to get the variable which had the minimun date of observation from data step

Kurt_Bremser — Mon, 30 Sep 2019 10:16:25 GMT

@Abimal_Zippi wrote:
That's great. Thanks a lot!.
One more question, the name of the variables are different, not like var_1, var_2.....can you advise me how can I use the transpose if the var names are different. Since I had more than 100, each with a different name.

If they form a close block in the dataset structure from left to right (no non-dates in between), then you can use this

firstvar--lastvar

to name the variables:

data have;
input id (firstvar secondvar thirdvar fourthvar lastvar) (:date9.);
format firstvar--lastvar date9.;
datalines;
1 10Jan2011 . 11Jan2012 . . 
2 12Feb2012 11Feb2013 . . .
3 21Mar2014 16Mar2014 18Mar2015 . .
4 . . 15Jan2016 10Feb2015 5Feb2015 
;

proc transpose data=have out=trans;
by id;
var firstvar--lastvar;
run;

proc sort data=trans;
by id col1;
run;

data want;
set trans;
where col1 ne .;
by id;
if first.id;
rename _name_=var col1=min_date;
run;

If such order is not the case, then you need to use an exhaustive list of variables; you could create such a list automatically from dictionary.columns:

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'HAVE' and format = 'DATE9.';
quit;

proc transpose data=have out=trans;
by id;
var &varlist.;
run;