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;*/
... View more