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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

14 REPLIES 14
Reeza
Super User

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;

Tom
Super User Tom
Super User

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

bncoxuk
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

art297
Opal | Level 21

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;

bncoxuk
Obsidian | Level 7

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.

art297
Opal | Level 21

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;

bncoxuk
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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'

bncoxuk
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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.

bncoxuk
Obsidian | Level 7

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.

art297
Opal | Level 21

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

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
  • 14 replies
  • 1665 views
  • 7 likes
  • 4 in conversation