## Return min date from multiple columns that is not zero

Solved
Regular Contributor
Posts: 182

# Return min date from multiple columns that is not zero

Hi

Imagine we have the following SAS table:

TreatmentDate1Date2Date3Effect
112/03/200202/02/201203
201/11/20092/10/201102
221/08/201329/03/20122/10/20121
111/10/2015026/12/20143

I am trying to get the "earliest" date in each row that is not equal to zero

the following code will not woke because the empty cells are coded as 0

proc sql;

create table want as

select

min (Date1, Date2, Date3) as min_date

from have;

quit;

Any ideas on how to do that (without replacing all zeros with missing values)?

best regards

Am

Accepted Solutions
Solution
‎04-18-2015 07:21 AM
Super User
Posts: 10,770

## Re: Return min date from multiple columns that is not zero

OK. Here is :

```data x;
input t (d1 d2 d3) (:  ddmmyy10.);
format      d1 d2 d3 ddmmyy10.;
cards;
1     12/03/2002     02/02/2012     01/01/1960
2     01/11/2009     2/10/2011     01/01/1960
2     21/08/2013     29/03/2012     2/10/2012
1     11/10/2015     01/01/1960     26/12/2014
;
run;
data want;
set x;
min=999999;
array x{*} d: ;
do i=1 to dim(x);
if x{i} lt min  and x{i} ne 0 then min=x{i};
end;
format min ddmmyy10.;
drop i;
run;
```

Xia Keshan

All Replies
Super User
Posts: 10,770

## Re: Return min date from multiple columns that is not zero

I am a little curious that how can you get 0 if they are all date variables . Which format are you using to these variables.

min=999999;

array x{*} date: ;

do i=1 to dim(x);

if x{i} lt min  and x{i} ne 0 then min=x{i};

end;

Regular Contributor
Posts: 182

## Re: Return min date from multiple columns that is not zero

Xia

Point well take, that was actually the excel file, when importing to SAS it is converting to number of days after 1 jan 1960.

I am not very familiar with SAS arrays, I guess I will have to read a bit on the subject,

Regards

Regular Contributor
Posts: 182

## Re: Return min date from multiple columns that is not zero

I am still not sure how to use the array to create a forth column with the min date, can you please elaborate?

Regards

Solution
‎04-18-2015 07:21 AM
Super User
Posts: 10,770

## Re: Return min date from multiple columns that is not zero

OK. Here is :

```data x;
input t (d1 d2 d3) (:  ddmmyy10.);
format      d1 d2 d3 ddmmyy10.;
cards;
1     12/03/2002     02/02/2012     01/01/1960
2     01/11/2009     2/10/2011     01/01/1960
2     21/08/2013     29/03/2012     2/10/2012
1     11/10/2015     01/01/1960     26/12/2014
;
run;
data want;
set x;
min=999999;
array x{*} d: ;
do i=1 to dim(x);
if x{i} lt min  and x{i} ne 0 then min=x{i};
end;
format min ddmmyy10.;
drop i;
run;
```

Xia Keshan

🔒 This topic is solved and locked.