Create SAS data set conditionally on other data sets

Question

I have 6 identical SAS data sets. They only differ in terms of the values of the observations.

How can I create one output data, which finds the maximum value across all the 6 data sets for each cell?

The update statement seems a good candidate, but it cannot set a condition.

data1

v1 v2 v3
1  1  1
1  2  3

data2

v1 v2 v3
1  2  3
1  1  1

Result

v1 v2 v3
1  2  3
1  2  3

Answer 1

If need be the following could be automated by "PUT" statements or variable arrays.
***ASSUMES DATA SETS ARE SORTED BY ID;

Data test; 
 do until(last.id);
   set a b c;
    by id;
 if v1 > updv1 then updv1 = v1;
 if v2 > updv2 then updv2 = v2;
 if v3 > updv3 then updv3 = v3;
 end;
drop v1-v3;
rename updv1-updv3 = v1-v3;
run;

To provide a more complete solution to Rico's question(assuming 6 datasets e.g. d1-d6) one could do it this way:

  Data test; 
  array v(*) v1-v3;
  array updv(*) updv1-updv3;
  do until(last.id);
   set d1-d6;
   by id;
    do i = 1 to dim(v);
      if v(i) > updv(i) then updv(i) = v(i);
    end;
  end;
  drop v1-v3;
  rename updv1-updv3 = v1-v3;
  run;


  proc print; 
  var id v1-v3;
  run;

Answer 2

See below. For a SAS beginner might be too complex. I hope the comments do explain it a bit.

/* macro rename_cols_opt to generate cols_opt&n variables 
 - cols_opt&n contains generated code for dataset RENAME option for a given (&n) dataset
*/
%macro rename_cols_opt(n);
    %global cols_opt&n max&n;

    proc sql noprint;
    select catt(name, '=', name, "&n") into: cols_opt&n separated by ' '
    from dictionary.columns
        where libname='WORK' and memname='DATA1'
            and upcase(name) ne 'MY_ID_COLUMN'
    ;
    quit;

%mend;


/* prepare macro variables = pre-generate the code */
%rename_cols_opt(1)
%rename_cols_opt(2)
%rename_cols_opt(3)
%rename_cols_opt(4)
%rename_cols_opt(5)
%rename_cols_opt(6)

/* create macro variable keep_list containing names of output variables to keep (based on DATA1 structure, the code expects those variables in other tables as well */
proc sql noprint;
select trim(name) into: keep_list separated by ' '
from dictionary.columns
    where libname='WORK' and memname='DATA1'
;
quit;
%put &keep_list;


/* macro variable maxcode contains generated code for calculating all MAX values */
proc sql noprint;
select cat(trim(name), ' = max(of ', trim(name), ":)") into: maxcode separated by '; '
from dictionary.columns
    where libname='WORK' and memname='DATA1'
        and upcase(name) ne 'MY_ID_COLUMN'
;
quit;
%put "&maxcode";

data result1 / view =result1;
merge
    data1 (in=a rename=(&cols_opt1)) 
    data2 (in=b rename=(&cols_opt2))
    data3 (in=b rename=(&cols_opt3))
    data4 (in=b rename=(&cols_opt4))
    data5 (in=b rename=(&cols_opt5))
    data6 (in=b rename=(&cols_opt6))
;
by MY_ID_COLUMN;
&maxcode;
keep &keep_list;
run;

/* created a datastep view, now "describing" it to see the generated code */
data view=result1;
describe;
run;

Answer 3

Here's another attempt that is scalable against any number of datasets and variables. I've added in an ID variable this time as well. Like the answer from @vasja, there are some advanced techniques used here. The 2 solutions are in fact very similar, I've used 'call execute' instead of a macro to create the view. My solution also requires the dataset names to be stored in a dataset.

/* create dataset of required dataset names */
data datasets;
input ds_name $;
cards;
data1
data2
;
run;

/* dummy data */
data data1;
input id v1 v2 v3;
cards;
10  1  1  1
20  1  2  3
;
run;

data data2;
input id v1 v2 v3;
cards;
10  1  2  3
20  1  1  1
;
run;

/* create dataset, macro list and count of variables names */
proc sql noprint;
create table variables as 
select name as v_name from dictionary.columns 
            where libname='WORK' and upcase(memname)='DATA1' and upcase(name) ne 'ID';
select name, count(*) into  :keepvar separated by ' ', 
                            :numvar 
            from dictionary.columns
            where libname='WORK' and upcase(memname)='DATA1' and upcase(name) ne 'ID';
quit;

/* create view that joins all datasets, renames variables and calculates maximum value per id */
data _null_;
set datasets end=last;
if _n_=1 then call execute('data data_all / view=data_all; merge');
    call execute (trim(ds_name)|| '(rename=(');
        do i=1 to &numvar.;
        set variables point=i;
        call execute(trim(v_name)||'='||catx('_',v_name,_n_));
        end;
        call execute('))');
if last then do;
        call execute('; by id;');
        do i=1 to &numvar.;
        set variables point=i;
        call execute(trim(v_name)||'='||'max(of '||trim(v_name)||':);');
        end; 
        call execute('run;');
end;
run;

/* create dataset of maximum values per id per variable */
data result (keep=id &keepvar.);
set data_all;
run;