BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xiaoqiuyin
Calcite | Level 5

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:

1 ACCEPTED SOLUTION

Accepted Solutions
Dreamer
Obsidian | Level 7

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

16 REPLIES 16
Dreamer
Obsidian | Level 7

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.

xiaoqiuyin
Calcite | Level 5

thanks, my library is WORK, but no rows selected

Haikuo
Onyx | Level 15

'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. 

xiaoqiuyin
Calcite | Level 5

Thank you Hai Kuo:) it works well.

Reeza
Super User

Please mark the question answered.

RyanSimmons
Pyrite | Level 9

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?

Reeza
Super User

SET &MEMNAMES.


You need to add the library to that line


SET FAKENAME.&MEMNAMES.

RyanSimmons
Pyrite | Level 9

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;

Reeza
Super User

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.

RyanSimmons
Pyrite | Level 9

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.

Reeza
Super User

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;

Steelers_In_DC
Barite | Level 11

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;

xiaoqiuyin
Calcite | Level 5

It works perfect!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 16 replies
  • 10068 views
  • 2 likes
  • 7 in conversation