topic Combine hundreds of *.sas7bdat in SAS Programming

Combine hundreds of *.sas7bdat

deleted_user — Fri, 03 Oct 2008 03:14:00 GMT

Hi, I wanna combine hundreds of *.sas7bdat in one folder on Windows Platform. The names of datasets have no rule. How can I do put them together using data steps?
Thank you for your time. Message was edited by: Jun

Re: Combine hundreds of *.sas7bdat

LinusH — Fri, 03 Oct 2008 07:10:19 GMT

What do you mean by putting then together?
Have they the same table structure (column names/types)?

You are probably looking into use DICTIONARY.TABLES from proc sql. There are several posts in the forums on this topic, as well as good documentation on support.sas.com.

Good luck!

Linus

Re: Combine hundreds of *.sas7bdat

deleted_user — Fri, 03 Oct 2008 19:21:20 GMT

Thank you for your reply, Linus.
The hundreds of datasets(*.sas7bdat) have same structure and are placed in same folder.
Could you give me some code to merge them vertically?
Data step is preferred, and PROC SQL is OK too.

Thank you.

Re: Combine hundreds of *.sas7bdat

Cynthia_sas — Fri, 03 Oct 2008 20:34:13 GMT

Hi:
To concatenate tables together (or, in your terms, to join them vertically (as opposed to merging them), you could use either PROC APPEND or a SET statement in a DATA step.
So, if you had these two tables (for example) and they both had the same variables NAME and AGE, then you could contenate them together:
[pre]
TABLE1
NAME AGE
Alan 15
Bob 14
Carla 15
********************

TABLE2
NAME AGE
Dave 17
Edna 16
Fiona 14
********************
[/pre]

...with this program
[pre]
data newtable;
set table1
table2;
run;
[/pre]

would result in NEWTABLE containing:
[pre]
NEWTABLE
NAME AGE
Alan 15
Bob 14
Carla 15
Dave 17
Edna 16
Fiona 14
[/pre]

You could also use PROC APPEND, or PROC SQL.

If you want the list of DATASET names for the SET to be populated dynamically, then you would have to follow Linus' suggestion and investigate the use of DICTIONARY.TABLES and some simple SAS Macro processing (the use of INTO within an SQL query) to give you the names of the tables that you want to concatenate together.

These papers may help you get started on finding out some uses for DICTIONARY.TABLES:
http://www2.sas.com/proceedings/sugi30/070-30.pdf
http://www2.sas.com/proceedings/sugi29/237-29.pdf
http://www.lexjansen.com/pharmasug/2006/tutorials/tu03.pdf
http://www.lexjansen.com/pharmasug/2005/posters/po31.pdf
http://www.sugme.org/sugme_dictionary.pdf

If you know about SAS Macro processing, then the program below should be fairly simple to follow. If you don't know about SAS Macro processing, then I suggest that you investigate and learn about the SAS Macro facility before implementing a production solution.

The program below creates 3 datasets (copies of SASHELP.CLASS) in the WORK library and then the PROC SQL step creates a macro variable containing the names of the files just created based on the LIBNAME='WORK' and MEMNAME contains 'CLASS' (your criteria may be different for choosing). Finally, the macro variable created in the PROC SQL step is used in a SET statement.

cynthia
[pre]
** 1) Make Some Data (you do not need this step);
data class_one;
set sashelp.class;
fileind = 1;
run;

data class_two;
set sashelp.class;
fileind = 2;
run;

data class_three;
set sashelp.class;
fileind = 3;
run;

** 2) Discover names of all "CLASS" files in Work;
** LIBNAME AND MEMNAME values should be uppercased.;
** Your selection criteria (WHERE clause) may be different.;
proc sql;
select catt(libname,'.',memname)
into :alldata separated by ' '
from dictionary.tables
where libname = 'WORK' and
memname contains 'CLASS';
quit;

%put The names of the files to concatenate are: &alldata;

** 3) Use the macro variable created above in a SET statement ;
** and verify that the data was created correctly;
data vertical;
set &alldata;
run;

proc freq data=vertical;
title 'After Macro method';
tables fileind;
run;
[/pre]

cynthia

Re: Combine hundreds of *.sas7bdat

deleted_user — Fri, 03 Oct 2008 23:40:29 GMT

Got you! Thank you for your help.

I learned a lot from you all.

Re: Combine hundreds of *.sas7bdat

Patrick — Sat, 04 Oct 2008 00:13:09 GMT

Just one more thing: Why duplicate data if there are views?
And if there are more SAS tables in the future instead of processing everything "physically" you can just rebuild the view.
HTH
Patrick

/*libname MyLib 'path to your directory';*/
libname MyLib (sasuser);

proc sql noprint;
select catx('.',libname,memname) into :TblList separated by ' '
from dictionary.members
where libname='MYLIB' and memtype='DATA'
;
%put The following tables are in the library:;
%put &TblList;
quit;

data work.MyView / view=work.MyView;
set &TblList;
run;