Help using Base SAS procedures

How to append all dataset in one library

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to append all dataset in one library

Hello all,

I have about 100 datasets (with different names) in one library. All datasets have same format and same variables. I would like to append them into one file, is there any quick way to do that? :smileyconfused::smileyconfused::smileyconfused:


Accepted Solutions
Solution
‎01-08-2015 03:42 PM
Frequent Contributor
Posts: 89

Re: How to append all dataset in one library

Hope this will work. Just replace <<Libname>> with your library name. If required you can also filter table names in where condition:

PROC SQL;

SELECT MEMNAME INTO : MEMNAMES SEPERATED BY ' ' from dictionary.tables where libname='<<LIBNAME>>';quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

SemicolonWikipedia: The semicolon or semi-colon is a punctuation mark that separates major sentence elements. A semicolon can be used between two closely related dependent clauses, provided they are not already joined by a coordinating conjunction. Semicolons can also be used in place of commas to separate items in a list, particularly when the elements of that list contain commas.

View solution in original post


All Replies
Solution
‎01-08-2015 03:42 PM
Frequent Contributor
Posts: 89

Re: How to append all dataset in one library

Hope this will work. Just replace <<Libname>> with your library name. If required you can also filter table names in where condition:

PROC SQL;

SELECT MEMNAME INTO : MEMNAMES SEPERATED BY ' ' from dictionary.tables where libname='<<LIBNAME>>';quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

SemicolonWikipedia: The semicolon or semi-colon is a punctuation mark that separates major sentence elements. A semicolon can be used between two closely related dependent clauses, provided they are not already joined by a coordinating conjunction. Semicolons can also be used in place of commas to separate items in a list, particularly when the elements of that list contain commas.

Occasional Contributor
Posts: 7

Re: How to append all dataset in one library

thanks, my library is WORK, but no rows selected

Occasional Contributor
Posts: 7

Re: How to append all dataset in one library

Respected Advisor
Posts: 3,124

Re: How to append all dataset in one library

's method is much better:

PROC SQL;

SELECT distinct /* just in case*/ MEMNAME INTO : MEMNAMES SEPERATED BY ' ' from dictionary.tables where libname='WORK' /*NOT <<WORK>>*/;quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

BTW, anything involving 'copy & paste' is not "Production" quality.

Haikuo

Update: if all of your tables are in WORK library, then 's question becomes relevant. So suppose if you have created all these files (copy from other permanent libraries doesn't make sense to me), then you will have the liberty to name them in a consistent way such as work_01, work_02 etc, you can then completely skip tapping metadata, statements like " set work:;" will have the job done. 

Occasional Contributor
Posts: 7

Re: How to append all dataset in one library

Thank you Hai KuoSmiley Happy it works well.

Grand Advisor
Posts: 16,916

Re: How to append all dataset in one library

Please mark the question answered.

Frequent Contributor
Posts: 98

Re: How to append all dataset in one library

This solution doesn't work for me, unless the datasets are already in my work library.

For example, this code works (assuming I have already input all my datasets into the SAS work library):

PROC SQL;

SELECT distinct MEMNAME INTO : MEMNAMES SEPERATED BY ' ' from dictionary.tables where libname='WORK';

quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

However, when I try to specify a different library, using the following code:

libname FAKENAME 'C:\Path';

PROC SQL;

SELECT MEMNAME INTO : MEMNAMES SEPERATED BY ' ' from dictionary.tables where libname='FAKENAME';

quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

SAS gives me error messages in the DATA step.

ERROR: File WORK.FILE1.DATA does not exist.

ERROR: File WORK.FILE2.DATA does not exist.

ERROR: File WORK.FILE3.DATA does not exist.

ERROR: File WORK.FILE4.DATA does not exist.

...etc.

So, it is looking for all of the datasets in the WORK library, despite me setting the libname to FAKENAME. How do I get around this behavior? This exact chunk of code was accepted as the answer, but it clearly doesn't actually work the way it is supposed to, unless you happen to already having everything in the WORK library.Otherwise, I can't find a way to get it to work. Adding the libname into the SET statement in the DATA step does not get around this issue. Further, using %put &memnames, I can see that PROC SQL hasn't appended any libname in front of the data file names at all.

Anyone have a solution? How did it work for the OP and not for me?

Grand Advisor
Posts: 16,916

Re: How to append all dataset in one library

SET &MEMNAMES.


You need to add the library to that line


SET FAKENAME.&MEMNAMES.

Frequent Contributor
Posts: 98

Re: How to append all dataset in one library

As I said in my post, "Adding the libname into the SET statement in the DATA step does not get around this issue."

Running

DATA WANT;

SET FAKENAME.&MEMNAMES.;

RUN;

Gives me the same exact error message (and, yes, I have made sure that the libname is properly defined):


64

65   DATA WANT;

66

67   SET FAKENAME.&MEMNAMES.;

ERROR: File WORK.FILE1.DATA does not exist.

ERROR: File WORK.FILE2.DATA does not exist.

ERROR: File WORK.FILE3.DATA does not exist

ERROR: File WORK.FILE4.DATA does not exist

68

69   RUN;

Grand Advisor
Posts: 16,916

Re: How to append all dataset in one library

Right, because it would only work for the first one, so you need Phil's solution to add it in to all data sets.

The premise is correct, add the libname reference because the default is WORK. Since yours is not work you need to explicitly identify it.

Frequent Contributor
Posts: 98

Re: How to append all dataset in one library

I'm a bit confused by what you mean, Reeza. I DID explicitly identify the libname.

I just don't quite understand why the method in the accepted answer doesn't work. You say that it would only work for "the first one" (unless all of the datasets are in the WORK library). In that case, Dreamer's answer is incorrect, and shouldn't be the accepted answer to the original post.

Grand Advisor
Posts: 16,916

Re: How to append all dataset in one library

It worked for the OP question, because their library was WORK and they get to determine the Correct Answer.  That doesn't mean its correct for your situation or all situations, or even the best answer.

The modification to Dreamer's answer to make it applicable to your situation is below.

PROC SQL;

SELECT catx('.', libname, MEMNAME) INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables where libname='<<LIBNAME>>';quit;

DATA WANT;

SET &MEMNAMES.;

RUN;

Valued Guide
Posts: 854

Re: How to append all dataset in one library

I have done the following to accomplish the same thing.  I'm guessing there is a better way but I know this will work.

proc datasets lib='libname' nolist;contents data=_all_ noprint out='new_dataset';quit;run;

proc sql;

     create table table_names as

     select DISTINCT MEMNAME

     from 'new_dataset';

This will give you all of the table names, copy and past them to get something like this:

data want;

set

table1

table2

table3

table...;

run;

Occasional Contributor
Posts: 7

Re: How to append all dataset in one library

It works perfect!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 747 views
  • 2 likes
  • 7 in conversation