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:
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. →
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. →
thanks, my library is WORK, but no rows selected
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.
Thank you Hai Kuo:) it works well.
Please mark the question answered.
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?
SET &MEMNAMES.
You need to add the library to that line
SET FAKENAME.&MEMNAMES.
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;
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.
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.
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;
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;
It works perfect!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.