How to find min and max date across columns in SAS data set?-CodePudding

I have a data set of following type

data have;
input subjid  var1$ var2$ var3$ var4$ var5$ var6$;
datalines;
121 23jan2022 24jan2022 20jan2022  24jan2022 26jan2022 25jan2022 
122 20jan2022 22jan2022 26jan2022  28jan2022 23jan2022 27jan2022 
;
run;

How can find the minimum date in all row across var1 to var3 and maximum date across var4 to var6 and then find the difference between maximum and minimum date.

The expected outcome is

In first row, 6 is the difference of 26jan2022 (this is max of var4-var6) and 20jan2022 (this is min date of var1-var3)

Any help is appreciated.

CodePudding user response：

You can try this:

data have;
    length subjid $3. var1 $9. var2 $9. var3 $9. var4 $9. var5 $9. var6 $9.;
    input subjid $ var1 $ var2 $ var3 $ var4 $ var5 $ var6 $;
datalines;
121 23jan2022 24jan2022 20jan2022 24jan2022 26jan2022 25jan2022 
122 20jan2022 22jan2022 26jan2022 28jan2022 23jan2022 27jan2022 
;
run;

data want;
    set have;
    array var {*} var1 var2 var3 var4 var5 var6;
    array _var {*} _var1 _var2 _var3 _var4 _var5 _var6;

    do i=1 to dim(var);
        _var{i}=input(var{i}, date9.);
        if _var{i}=min(_var1, _var2, _var3) then var_min=var{i};
        if _var{i}=max(_var4, _var5, _var6) then var_max=var{i};
    end;
    var_diff=max(_var4, _var5, _var6)-min(_var1, _var2, _var3);

    format _var1 _var2 _var3 _var4 _var5 _var6 YYMMDD10.;
    drop i;
run;

CodePudding user response：

If the variables are actual date values.

data have;
  input subjid $  (var1-var6) (:date.);
  format var1-var6 date9.;
datalines;
121 23jan2022 24jan2022 20jan2022  24jan2022 26jan2022 25jan2022 
122 20jan2022 22jan2022 26jan2022  28jan2022 23jan2022 27jan2022 
;

Then just use the MIN() and MAX() functions to find the min and max dates. Once you have those simple subtraction will find the difference (range of dates).

data want;
  set have;
  mindate = min(of var1-var6);
  maxdate = max(of var1-var6);
  diff = maxdate-mindate;
  format mindate maxdate date9.;
run;

Results