SAS macro for reading various datasets with different formats and field name

[kllamitarey]

I want  a macro that allows to read sas dataset from a library 'athm'. I need a macro coz I have about more than  50 sas datasets  &X._reportdata_allyears_&i  and create a  data table  with common fields and later do some summary statistics. To break down, I want to run for 2012 to 2016.

Problem: Some of the tables have different coulmn names( such as  Numerator , num ,N , Nu, nume ) to mean the same thing. For example AB_reportdata_allyears_2012 has  a field  Numerator  and AB_reportdata_allyears_2013 has Num  to mean the same thing. In one dataset, the column 'Year ' is character and in other dataset, the column 'Year' is numeric. I want to make year to  a character value for all datasets.

I tried the following:

Try 1. 

libname athm  "C:\Users\Trial";

%macro do_yr(X,MY,s,e);
%do i=&s %to &e;
proc sql;
create table &x&i as

      select

                              sum(Num) as Num,

                              sum(Denom) as  Den,

                               sum(Num)/sum(Denom) as rate,
                                &MY,

                                &i as RY
        from athm.&X._reportdata_allyears_&i

                where  Result="All"
group by  &MY 
quit; ;
%end;

data &X;
set
%do j=&s %to &e %by 1;
&X&j
%if j=" " %then delete;

%end;
;run;

%mend do_year;
%do_year(AB,Year,2012,2016)

Try 2. 

%macro convert(Y,s,e);

/*Create datasets with a common fields and formats*/

data 

%do i=&s %to &e %by 1;

&Y_reportdata_allyears_Trial$i;

/* Reading many data sets to SAS workspace from the library ATHM*/

set athm.
%do i=&s %to &e %by 1;
&Y_reportdata_allyears_&i;
rename Numerator=Num;
rename Denominator=Deno;
year1=put(year,4.);
drop year;
rename Year1=year;
format year $4.;
run;
%end;
%end;
%mend convert;
%convert(AB,2012,2016);

/*Try 1. works for some but not all.  Try 2. Only create error message.*/

/*Please let me know if you have any questions. I will try to make a clear descriptions of the problems. I need your expertise to make this work. I'm open to suggestions and alternative apporaches;*/

[LinusH]

If you set a naming standard everything will be simpler. Rename using proc datasets or put views on top. Ultimately store all data on the same table which will minimise the need for complicated macro loop programming.

[ballardw]

If this were my project I would be strongly tempted to either rename in place as per @LinusH and if not practical as others use the data sets to make copies and rename the variables. A control data set with the name of the original dataset, new data set, original variable name and new or corrected data set name in data set would let you use a data step with call execute to copy the original data to the new set and then rename the variables.