topic Re: Return min date from multiple columns that is not zero in SAS Procedures

Return min date from multiple columns that is not zero

ammarhm — Sat, 18 Apr 2015 08:00:38 GMT

Imagine we have the following SAS table:

Treatment	Date1	Date2	Date3	Effect
1	12/03/2002	02/02/2012	0	3
2	01/11/2009	2/10/2011	0	2
2	21/08/2013	29/03/2012	2/10/2012	1
1	11/10/2015	0	26/12/2014	3

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

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

Ksharp — Sat, 18 Apr 2015 08:25:19 GMT

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;

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

ammarhm — Sat, 18 Apr 2015 08:32:49 GMT

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

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

ammarhm — Sat, 18 Apr 2015 09:04:19 GMT

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

Regards

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

Ksharp — Sat, 18 Apr 2015 11:21:54 GMT

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