SAS proc sql create column on vertical join

The basic approach I'd suggest is to assign a constant representing the year in each of the select queries ...

proc sql;
    create table data_all as    
        select 2000 as year, *
        from data_2000
        UNION ALL
        select 2001 as year, *
        from data_2001
        UNION ALL
        select 2002 as year, *
        from data_2002
    ;
quit;

You can then turn this into a macro as follows:

%macro join_data(dat=);
    proc sql;
        create table data_all as
            %let i = 1;
            %let cur_dat = %scan(&dat.,1);
            %do %while (&cur_dat. ne);
                %if &i. > 1 %then UNION ALL;
                select &cur_dat. as year, *
                from data_&cur_dat.
                %let i = %eval(&i. + 1);
                %let cur_dat = %scan(&dat.,&i.);
            %end;
        ;
    quit;       
%mend join_data;
%join_data(dat=2000 2001 2002);