Merging files using Macros

[bonedog]

Hi all! I am trying to teach myself how to merge large datasets using macros so I can save some time in the long run. I have been using this paper as a guide https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/057-29.pdf .

Using this code: 

filename indata pipe 'dir X:\SAVEFILE /b';

DATA file_list;
	length fname $8;
	infile indata truncover;
	input fname $8.;
	call symput ('num_files', put(_n_,2.));
RUN;

%MACRO fileread;
	%do j=1 %to &num_files;

DATA _null_;
	set file_list;
	if _n_=&j;
	call symput('filein',fname);
RUN;

data var_names;
	length x1-x7 $8;
	infile "X:\SAVEFILE\&filein" obs=1 missover DSD;
	input (x1-x7) ($);
RUN;

	%MACRO varnames;
		%do i=1 to 7;
		%global v&i;
		DATA _null_;
			set var_names;
			call symput("v&i",trim(x&i));
		RUN;
		%end;
	%mend varnames;

	%varnames;

DATA temp;
	infile "X:\SAVEFILE\&filein" firstobs=2 missover DSD;
	input (&v1 &v2 &v3 &v4 &v5 &v6 &v7) ($);
	cowID = &fname;
RUN;

%if &j=1 %then %do;
	DATA data_all;
		set temp;
	RUN;
	%end;
	%end;
%mend fileread;
%fileread;

I have been getting a log full of errors for each iteration of the 33 files I am trying to merge. A sample for one iteration is below.

ERROR: Expected %TO not found in %DO statement.
ERROR: A dummy macro will be compiled.
NOTE: Line generated by the invoked macro "FILEREAD".
2             %global v&i;         DATA _null_;             set var_names;             call
2  ! symput("v&i",trim(x&i));         RUN;         %end;     %mend varnames;      %varnames;  DATA
                                                                                  -
                                                                                  180
WARNING: Apparent invocation of macro VARNAMES not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "FILEREAD".
3     (&v1 &v2 &v3 &v4 &v5 &v6 &v7) ($);     cowID = &fname; RUN;
       -
       22
       76
ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, _ALL_, _CHARACTER_,
              _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

3  !  (&v1 &v2 &v3 &v4 &v5 &v6 &v7) ($);     cowID = &fname; RUN;
                                                     -
                                                     22
WARNING: Apparent symbolic reference V1 not resolved.
WARNING: Apparent symbolic reference V2 not resolved.
WARNING: Apparent symbolic reference V3 not resolved.
WARNING: Apparent symbolic reference V4 not resolved.
WARNING: Apparent symbolic reference V5 not resolved.
WARNING: Apparent symbolic reference V6 not resolved.
WARNING: Apparent symbolic reference V7 not resolved.
WARNING: Apparent symbolic reference FNAME not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, INPUT, PUT.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEMP may be incomplete.  When this step was stopped there were 0
         observations and 2 variables.
WARNING: Data set WORK.TEMP was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds



NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.DATA_ALL has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Can I have some help troubleshooting this code? I've been trying to go step by step through my log but from here I don't know where to go.

Thanks!

[ballardw]

%MACRO varnames;
		%do i=1 to 7;
		%global v&i;
		DATA _null_;
			set var_names;
			call symput("v&i",trim(x&i));
		RUN;
		%end;
	%mend varnames;

is the cause of this error.

ERROR: Expected %TO not found in %DO statement.

because of dependencies you likely need to fix that before we can tell if anything else actually needs to be addressed.

Generally it is a bad idea to place one macro definition inside another macro. Better is to get a macro working with parameters. Then you can test the behavior of the single macro without relying on the outer one working correctly.

I am not seeing any "merging" in the traditional SAS sense. If the idea is to read a bunch of identical structured text files then this is way complicated as you can use a number of techniques to read multiple external files in a single pass.

If you are going to use Macros learn to set OPTIONS MPRINT SYMBOLGEN (and possibly ) MLOGIC; before attempting to execute macros when you have problems. Those options will provide lots more detail in the log about the code generated.

Turn the options off using Options NOMPRINT NOSYMBOLGEN NOMLOGIC; after wards.

Note:

	length x1-x7 $8;
	infile "X:\SAVEFILE\&filein" obs=1 missover DSD;
	input (x1-x7) ($);

may not set long enough values for working variables using the approach. X1 to x7 will have length 8 .

So if the header row of your data set has values like Customer1 Customer2 Customer3 the X variable would all have the value "Customer". Meaning that your input is likely going to result in unexpected results. (Hint: what happens when the same variable name appears multiple times in a single INPUT statement?

Use of CALL SYMPUTX can cut down on the number of "trim" and "left" operations on values.

[PaigeMiller]

---

@bonedog wrote:

Hi all! I am trying to teach myself how to merge large datasets using macros so I can save some time in the long run. I have

---

In addition to what @ballardw has said:

I don't see any MERGE statement, and its really not clear what you are trying to do here, or why you even need two macros to do this.

The advice which I have for you and everyone is to create code that works without macros and without macro variables, for two of the input files. Get that to work. If you can't get that to work, you will never get a macro program to work. Once you have that working, then adding loops and macro variables inside a macro has a much better chance of working. So as step 1, please create working SAS code that does what you want for two of the data sets, without using macros and without using macro variables, and show that to us.