topic Re: How to append all dataset in one library in SAS Procedures

How to append all dataset in one library

xiaoqiuyin — Thu, 08 Jan 2015 20:31:04 GMT

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:

Re: How to append all dataset in one library

Dreamer — Thu, 08 Jan 2015 20:42:29 GMT

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

Re: How to append all dataset in one library

Steelers_In_DC — Thu, 08 Jan 2015 20:43:18 GMT

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;

Re: How to append all dataset in one library

Reeza — Thu, 08 Jan 2015 20:47:58 GMT

The different names don't have any naming convention? i.e. data1-data100 or dt_alabama dt_arizona?

Re: How to append all dataset in one library

xiaoqiuyin — Thu, 08 Jan 2015 21:43:56 GMT

It works perfect!

Re: How to append all dataset in one library

xiaoqiuyin — Thu, 08 Jan 2015 21:48:46 GMT

thanks, my library is WORK, but no rows selected

Re: How to append all dataset in one library

xiaoqiuyin — Thu, 08 Jan 2015 21:49:34 GMT

Re: How to append all dataset in one library

Haikuo — Thu, 08 Jan 2015 22:01:18 GMT

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

Re: How to append all dataset in one library

xiaoqiuyin — Thu, 08 Jan 2015 22:22:23 GMT

Thank you Hai Kuo:) it works well.

Re: How to append all dataset in one library

Reeza — Fri, 09 Jan 2015 03:49:02 GMT

Please mark the question answered.

Re: How to append all dataset in one library

RyanSimmons — Fri, 31 Jul 2015 16:34:03 GMT

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?

Re: How to append all dataset in one library

Reeza — Fri, 31 Jul 2015 16:43:31 GMT

SET &MEMNAMES.

You need to add the library to that line

SET FAKENAME.&MEMNAMES.

Re: How to append all dataset in one library

RyanSimmons — Fri, 31 Jul 2015 16:55:10 GMT

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):

65 DATA WANT;

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

69 RUN;

Re: How to append all dataset in one library

PhilC — Fri, 31 Jul 2015 17:21:35 GMT

I'm always doing this. This particular way I can have data set options applied to each dataset.

proc sql noprint; 
  select compress(libname || "." ||memname||' &DSoptions.')
    into: datasets separated by ' '
    from Dictionary.tables
    where libname="LIBNAME"  ; *<-- needs to be all caps;
quit;

Re: How to append all dataset in one library

Reeza — Fri, 31 Jul 2015 18:01:38 GMT

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.

Re: How to append all dataset in one library

RyanSimmons — Fri, 31 Jul 2015 18:06:48 GMT

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.

Re: How to append all dataset in one library

Reeza — Fri, 31 Jul 2015 19:17:44 GMT

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;