Help using Base SAS procedures

How to combine files with a macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

How to combine files with a macro

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?


Accepted Solutions
Solution
‎09-20-2011 01:30 PM
Super User
Super User
Posts: 6,502

How to combine files with a 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;

View solution in original post


All Replies
Super User
Posts: 17,899

How to combine files with a macro

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;

Super User
Super User
Posts: 6,502

How to combine files with a macro

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

Frequent Contributor
Posts: 131

How to combine files with a macro

HI Reeza, I just tested it and sorry that the SAS cannot pass the code (error) Smiley Sad

Super User
Super User
Posts: 6,502

How to combine files with a macro

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.

PROC Star
Posts: 7,363

How to combine files with a macro

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;

Frequent Contributor
Posts: 131

How to combine files with a macro

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.

PROC Star
Posts: 7,363

How to combine files with a macro

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;

Frequent Contributor
Posts: 131

How to combine files with a macro

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

Super User
Super User
Posts: 6,502

How to combine files with a macro

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'

Frequent Contributor
Posts: 131

How to combine files with a macro

Hi art, Tom,

If I just change the libname to 'PROJECT' and memname like 'data%'  (where libname eq "work" and memname like 'data%'Smiley Wink  ,   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

Solution
‎09-20-2011 01:30 PM
Super User
Super User
Posts: 6,502

How to combine files with a 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;

PROC Star
Posts: 7,363

How to combine files with a macro

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.

Frequent Contributor
Posts: 131

How to combine files with a macro

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.

PROC Star
Posts: 7,363

How to combine files with a macro

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

☑ This topic is solved.

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

Discussion stats
  • 14 replies
  • 407 views
  • 6 likes
  • 4 in conversation