Do LOOP not in a macro

[Phil_NZ]

Hi SAS Users,

Today I try to merge 34 sheets in one file excel together, however, my "do...to" does not work. I also cross-check whether "do...to" can be used outside a macro and the answer is yes.

Manually speaking, my code should be:

data merge1;
merge
sheet2_outx (keep type year s22)
sheet3_outx (keep type year s32)
sheet4_outx (keep type year s42)
sheet5_outx (keep type year s52)
/* It will be like that till sheet33*/
sheet34_out (keep type year s34)
/* sheet34 I just have sheet34_out instead of sheet34_outx and s34 instead of s342*/
by type year;
end;
run;

I  try to use a "DO to" to make the work become easier and shorter

data merge1;
 do i=1 to 33;
 merge 
  sheeti._outx(keep=type year si.2)
  ;
 by type year;
 end;
 
run;

And the error is that

28         data merge1;
29          do i=1 to 33;
30          merge
31           sheeti._outx(keep=type year si.2)
ERROR: Libref SHEETI is not assigned.
32           ;
33          by type year;
34          end;
35         
36         run;

NOTE: Compression was disabled for data set WORK.MERGE1 because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MERGE1 may be incomplete.  When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set WORK.MERGE1 was not replaced because this step was stopped.

I am not sure whether "Do...to"  in a macro is a must in this case?

Many thanks!

[Phil_NZ]

I also try to write a macro to deal with it but I feel something wrong, can you please help me to spot it out?

%macro merge (start=, end=);
  %local i;
  %do i=&start. %to &end.;
	%if (&i ne &start.) and (&i. ne &end.)%then %do;
	data merge1;
	 merge
	  sheet&i._outx(keep=type year s&i.2)
     by type year;
    run;
   %end;
   %else %if &i=&end. %then %do;
	data merge2;
	 merge
	  merge1
	  sheet&end._out(keep=type year s&end.)
     by type year;
    run;
   %else %if &i=&start. %then %do;
    data merge3;
	 merge
	  merge2
	  sheet&start.
	  ;
	by type;
	run;
	%end;
   %end;
%mend;

%merge(start=1, end=34);

Log is like that

45            %else %if &i=&start. %then %do;
ERROR: There is no matching %IF statement for the %ELSE.
ERROR: A dummy macro will be compiled.
46             data merge3;
47         	 merge
48         	  merge2
49         	  sheet&start.
50         	  ;
51         	by type;
52         	run;
53         	%end;
54            %end;
55         %mend;
2                                                          The SAS System                           16:18 Wednesday, January 6, 2021

56         
57         %merge(start=1, end=34);
           _
           180
WARNING: Apparent invocation of macro MERGE not resolved.

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

Many thanks in advance!

[andreas_lds · Accepted solution]

You can't create dataset names using a normal loop in a data-step. You could use macro-code, but i strongly recommend not write any macro-code until you fully understand the data-step.

In SAS you don't have sheets - this is a concept only valid in excel (and LibreOffice Calc), in SAS you have datasets, and while both look alike at a quick glance, they are totally different when it comes to using them. So, if all those sheet dataset are created by importing excel files, it is very likely that they all appear to have the same structure, but are different. You should, before you start writing the code merging all those datasets, have a look at the metadata, proc contents is a great help, to verify that at least type and year have the same data-type and the same length in all datasets. If you find differences it would be best to drop Excel as data source and switch to csv-files. Those files can be imported by using a data step granting you full control over the result.

And there is another problem with the loop: the name of the third variable in the keep-option differs from dataset to dataset. If the name of that variable is "s342" for the 34th dataset, you could use:

%macro merger;
  %local i;
  
  data merge1;
    merge
	  %do i = 1 %to 34;
	    sheet&i._outx(keep= type year s&i.2)
	  %end;
	;
	by type year;
  run;
%mend;

%merger;

[Phil_NZ]

Hi @andreas_lds !

Thank you very much for your reply!

The sheets I mentioned in the code is the name of the dataset from the previous code.

I conduct a code that work well and successfully. Because the structure of sheet1 and 34 are quite different from other sheets so I need to have 3 merge files. I am wondering if there is any way to optimize such a code.

And there is another interesting point to me. It is even I did not put the semicolon ";" at the end of macro merger, my SAS  even runs and did not announce any error or warning, so surprising to me!!

  %macro merger (start=, end=);
  %local i;
  
  data merge1;
     merge
	  %do i =&start.+1 %to &end.-1;
	    sheet&i._outx(keep= type year s&i.2)
	  %end;
	;
	by type year;
  run;

  data merge2;
     merge
	  merge1
	  sheet&end._out (keep=type year s&end.)
	  ;
	 by type year;
  run;
 data merge3;
     merge
	  sheet1
	  merge2 
	 ;
	 by type;
 run;
 %mend;

 %merger(start=1, end=34)

[Kurt_Bremser]

If a macro creates complete statements, no semicolon is needed after the call. Keep in mind that a macro can also be called to resolve to a word (e.g. a library name):

set %deptlib(&user.).dataset;

where you must not have a semicolon.

A semicolon is only needed if the code created by the macro demands it, like

set
  %datasets(&today.)
;