BookmarkSubscribeRSS Feed
kllamitarey
Obsidian | Level 7

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

 

2 REPLIES 2
LinusH
Tourmaline | Level 20
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.
Data never sleeps
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 942 views
  • 0 likes
  • 3 in conversation