I have a practical question to combine a few files. Suppose the file names are:
data200901
data200902
data200903
data200904
data200905
data200906
data200907
data200908
data200909
data200910
data200911
data200912
How can I use some data step to combine these files? Art has showed a way of doing this using data20: Is there a way using a simple macro?
You should have also seen a message that the macro variable TABLE_LIST was not defined.
This is because the query did not find any datasets that matched your query and so TABLE_LIST will never get defined.
Most likely it is because of your use of lowercase strings. I know for sure that the LIBNAME value will always be uppercase. I think that the MEMNAME value will also always be in uppercase.
Why not just start by listing out what tables SAS does show in the DICTIONARY.TABLES view?
proc print data=dictionary.tables;
var libname memname ;
run;
You also need to modify the catcatenation of libname and membername to eliminate trailing spaces on the LIBNAME variable.
%macro merge_multiple;
%let table_list=;
proc sql noprint;
select catx('.',libname,memname) into : table_list separated by " "
from dictionary.tables
where libname eq "WORK" and memname like 'DATA%';
quit;
data work.want;
%if &sqlobs %then %do;
set &table_list;
%end;
run;
%mend merge_multiple;
data work.data1 ; ds=1; run;
data work.data2; ds=2; run;
%merge_multiple;
If they're all in the same library, scan the dictionary tables for names that fit your criteria using a SQL statement and then use that macro variable in your set statement.
You probably need to play with the cases to match a bit, but it should get you started.
UNTESTED
%macro merge_multiple;
*get list of names;
proc sql;
select into : table_list
from dictionary.tables
where libname='WORK' and memname like 'DATA%';
quit;
data want;
set &table_list;
run;
%mend merge_multiple;
I think that you are asking if you can loop over the formatted values of variable in macro code.
In general the answer is no, but there have been many discussions about ways to work around this.
Here is one from earlier this year.
http://listserv.uga.edu/cgi-bin/wa?A2=ind1103C&L=sas-l&D=0&P=24621
HI Reeza, I just tested it and sorry that the SAS cannot pass the code (error)
You need to add a SEPARATED BY ' ' claus to the INTO cluase of the select statement. Otherwise SQL will only put the value from the first observation it pulls into the macro variable.
And, while it will work as a macro, the macro itself is totally not needed, just the macro variable. The following does exactly the same thing:
*get list of names;
proc sql;
select into : table_list
separated by " "
from dictionary.tables
where libname='WORK' and memname like 'DATA%';
quit;
data want;
set &table_list;
run;
I am sure the code should work. But it did not work in my case.
When the program ran, SAS said:
ERROR 73-322: Expecting an INTO.
The error points to the place of into:.
I guess this is caused by the change of SAS default setting. The default directory is not 'work', but it is 'project', which is changed by someone who share the SAS software.
In this case, what adjustment should I make in consideration of the default directory 'project'? I tried to do it myself, but not working.
It didn't work, as the other code wouldn't work, because I didn't check the other code to see if it was correct.
It was missing any variables to select. The following should work:
proc sql noprint;
select memname into : table_list
separated by " "
from dictionary.tables
where libname='SASHELP' and memname like 'CL%'
;
quit;
Hi art, thanks indeed for your help. Nearly there, but there is an error as below. In our company, the SAS software is shared and the default directory has been changed to PROJECT, not SAS's default directory work. How to adapt the code?
MPRINT(MERGE_MULTIPLE): data work.want;
MPRINT(MERGE_MULTIPLE): set CLASS CLASSFIT CLNMSG;
ERROR: File PROJECT.CLASSFIT.DATA does not exist.
ERROR: File PROJECT.CLNMSG.DATA does not exist.
MPRINT(MERGE_MULTIPLE): run;
/*Code:*/
%macro merge_multiple;
proc sql noprint;
select memname into : table_list separated by " "
from dictionary.tables
where libname='SASHELP' and memname like 'CL%';
quit;
data work.want;
set &table_list;
run;
%mend merge_multiple;
%merge_multiple
Why would you expect a dataset from the SASHELP library to necessarily exist in the PROJECT library?
Change the query to match your situation.
This is the clause that needs to change:
where libname='SASHELP' and memname like 'CL%'
You should change SASHELP to PROJECT to search for members in the PROJECT library.
You should also change the rest of the conditional to find the particular datasets that you want.
For example if you want to find every dataset in project then just leave it at:
where libname='PROJECT'
Hi art, Tom,
If I just change the libname to 'PROJECT' and memname like 'data%' (where libname eq "work" and memname like 'data%';) , then SAS produced the error as below again:
MPRINT(MERGE_MULTIPLE): data work.want;
WARNING: Apparent symbolic reference TABLE_LIST not resolved.
MPRINT(MERGE_MULTIPLE): set &table_list;
ERROR: File PROJECT.TABLE_LIST.DATA does not exist.
MPRINT(MERGE_MULTIPLE): run;
SAS code:
%macro merge_multiple;
proc sql noprint;
select libname||"."||memname into : table_list separated by " "
from dictionary.tables
where libname eq "work" and memname like 'data%';
quit;
data work.want;
set &table_list;
run;
%mend merge_multiple;
%merge_multiple
You should have also seen a message that the macro variable TABLE_LIST was not defined.
This is because the query did not find any datasets that matched your query and so TABLE_LIST will never get defined.
Most likely it is because of your use of lowercase strings. I know for sure that the LIBNAME value will always be uppercase. I think that the MEMNAME value will also always be in uppercase.
Why not just start by listing out what tables SAS does show in the DICTIONARY.TABLES view?
proc print data=dictionary.tables;
var libname memname ;
run;
You also need to modify the catcatenation of libname and membername to eliminate trailing spaces on the LIBNAME variable.
%macro merge_multiple;
%let table_list=;
proc sql noprint;
select catx('.',libname,memname) into : table_list separated by " "
from dictionary.tables
where libname eq "WORK" and memname like 'DATA%';
quit;
data work.want;
%if &sqlobs %then %do;
set &table_list;
%end;
run;
%mend merge_multiple;
data work.data1 ; ds=1; run;
data work.data2; ds=2; run;
%merge_multiple;
Which files are you trying to read? I had thought you were trying to get them from sashelp.
What directory are they in? The code I had sent was to get the files from sashelp.
Hi art, your code is also correct.
As Tom said, I should have put capital letters in the libname and memname statement.
Thank you very much.
I think you simply need to concatenate libname within your proc sql code. e.g., assuming you really do want to identify and concatenate all files in sashelp that start with CL, then you could use:
%macro merge_multiple;
proc sql noprint;
select libname||"."||memname into : table_list
separated by " "
from dictionary.tables
where libname eq "SASHELP" and memname like 'CL%';
quit;
data work.want;
set &table_list;
run;
%mend merge_multiple;
%merge_multiple
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.