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

kllamitarey — Tue, 18 Oct 2016 15:48:16 GMT

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;*/

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

LinusH — Tue, 18 Oct 2016 19:40:47 GMT

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.

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

ballardw — Tue, 18 Oct 2016 21:24:53 GMT

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.

topic SAS macro for reading various datasets with different formats and field name in SAS Data Management

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

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

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