topic Re: vertically combine many data sets in SAS Programming

vertically combine many data sets

changxuosu1 — Thu, 08 Mar 2018 05:12:18 GMT

Hi Everyone, thank you for reading the post.

I combined 24 datasets using the following code:

data conso.agloan;
set conso.agloan1993 conso.agloan1994 conso.agloan1995 conso.agloan1996 conso.agloan1997 conso.agloan1998 conso.agloan1999 conso.agloan2000 conso.agloan2001 conso.agloan2002 conso.agloan2003 conso.agloan2004 conso.agloan2005 conso.agloan2006 conso.agloan2007 conso.agloan2008 conso.agloan2009 conso.agloan2010 conso.agloan2011 conso.agloan2012 conso.agloan2013 conso.agloan2014 conso.agloan2015 conso.agloan2016;
run;

It works. But is there any trick to do it smarter without actually typing each dataset, something like

data conso.agloan;

set conso.agloan1993 - conso.agloan2016;

run;

Thank you in advance.

Re: vertically combine many data sets

novinosrin — Thu, 08 Mar 2018 05:23:14 GMT

did your something like

data conso.agloan;

set conso.agloan1993 - conso.agloan2016;

run;

work?????

Re: vertically combine many data sets

ballardw — Thu, 08 Mar 2018 16:10:13 GMT

Or if you want ALL of the agloan sets in the library:

data conso.agloan;

set conso.agloan: ; /* note the : that indicates the list operator */

run;

If your individual sets are largish there may be a time penalty due to the way SET works and a series of Proc Append Calls may be quicker to execute. Unfortunately Proc Append only uses two data sets and not the shortcut list.

Re: vertically combine many data sets

SuryaKiran — Thu, 08 Mar 2018 16:22:52 GMT

If all the tables are in specific library and have a defined prefix or suffix then you can get those table list by running query on dictionary.tables

proc sql;
select distinct memname into: SET_Tables separated by " "
	from dictionary.tables
	where libname="CONSO" and memname like 'AGLOAN%';
quit;
data want;
set &SET_Tables;
run;

Re: vertically combine many data sets

changxuosu1 — Thu, 08 Mar 2018 17:02:09 GMT

oh my God! it works!!! it's so hilarious.. 🙂 thanks!

Re: vertically combine many data sets

changxuosu1 — Thu, 08 Mar 2018 17:03:04 GMT

Thank you ballardw! it works perfectly!

Re: vertically combine many data sets

changxuosu1 — Thu, 08 Mar 2018 17:04:58 GMT

Thank you so much Suryakiran~ It works great~

Re: vertically combine many data sets

changxuosu1 — Thu, 08 Mar 2018 17:13:15 GMT

can i choose two solutions? this works great as well. ( I changed select distinct memname into 'conso.'||left(memname) to customize to my libary)