BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
LinusH
Tourmaline | Level 20
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
Data never sleeps
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Got you! Thank you for your help.

I learned a lot from you all.
Patrick
Opal | Level 21
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;

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1744 views
  • 0 likes
  • 4 in conversation