Correctly use DO loop in SET statement to concatenate multiple datasets together

[rosegarden81]

Hi All,

I have a path where there are 9 datasets (one for each year). I create counts of a common variable and create a separate data set that stores the counts. I am then trying to set all of these new count datasets into one dataset (without having to hard code). Below is an example:

/* Create counts from raw individual data files */

proc sql;

create table count_year2010 AS

SELECT count(patient) as CountPatient

FROM year2010;

QUIT;

proc sql;

create table count_year2011 AS

SELECT count(patient) as CountPatient

FROM year2011;

QUIT;

.

.

.

I do the above for 9 datasets (using a macro) that creates these new datasets storing count. 

My next step (if I were to hardcode would be):

data set_hardcoded;

set count_year2010  count_year2011 count_year2012 count_year2013 count_year2014 count_year2015 ...... count_year2018;

run;

But instead of hardcoding all these individual datasets in the set statement, I am trying to find a way to use a DO loop such that I can append "_count" in the beginning of year2010, year2011 etc and use them in the set statement.

I tried the following:

%let inputdatasets = year2010 year2011 year2012 year2013 year2014 year2015 year2016 year2017 year2018;

%let toappend=_count;

%macro test1;

data test;  /* in an attempt at creating the same result as set_hardcoded above produces */

%do i = 1 to 9;

%let eachdata = %scan(&inputdatasets , &i);

%let cat_var = %sysfunc(CATS(&toappend, &eachdata));

%end;

set &cat_var ;  /* this does not work ; it only takes the last variable in inputdatasets ???*/

%mend;

%temp;

The above is not working and I am unable to simulate the want portion. Any ideas on how to approach this would be greatly appreciated!

Regards

[VDD]

this is not a do loop but it works better.    change the count_year to the name of the datasets that you have. 

proc sql ;		
	/* get all the datafile names into a macro var */
	select 		
		trim(libname) || '.' || memname into :dataset_vars separated by ' '
	from dictionary.tables
	where upcase(libname)="WORK"
	and upcase(memname) like 'COUNT_YEAR%' ;
quit;

data Count_years;
	set &dataset_vars;
run;

You will find this piece of code to be of great value in your future. 

[rosegarden81]

I will save this code for future purposes..Looks nice..

[Tom · Accepted solution]

Looks like you are doing a lot of work for a simple task.  To combine datasets that are named with a numeric suffix just use a member name list.

data want ;
  set count_year2010 - count_year2018 ;
run;

If you did need to use macro %DO loop then make sure to use it to only generate the list of dataset names, and NOT generate the SET or semicolon.

data want;
  set
%do yr=2010 %to 2018 ;
   count_year&yr
%end;
  ;
run;

[rosegarden81]

Thank you so much for this! I was able to implement this code per your recommendation..

[Astounding]

Instead of complicating this with macro language, you could simply use:

data not_hard_coded;

set count_year20: ;

run;

That will read all data set names that start with "count_year20"

[Reeza]

You don't need a loop, use a naming convention and you can use the colon short cut to reference all at once. 

I'd probably recommend possibly doing this at the beginning and using by group processing instead of macros. Otherwise your code will all be macros. 

data want;
set count_year: ;
run;

If you can back up a few steps, create a view which is the combination of all the data and then do the SQL or PROC MEANS. 

The view is only called when you run the proc means so it doesn't take up space or anything and if your data sizes aren't super large this is more efficient. 

data demo / view=demo;
set year2010-year2018 indsname=source;
dsn=source;
run;

proc sql;
create table summary_counts as
select dsn, count(patient) as CountPatient, 
from demo
group by dsn;
quit;