%Do Statement combination

[Antonio598]

How do I create one combination of the following? I need to merge IN7.VAPP2201, IN8.VAPP2202

IN9.VAPP2203 .... and so forth. However this macro doesn't work in creating only one combination and the leading 0 for 'i' is removed in the output. Any help is appreciated

%MACRO MERGED;

PROC SQL ;

CREATE VIEW MERGE AS

SELECT * FROM IN1.VAPP2120
%do n=7 %to 33;
%do i=01 %to 26;
outer union corr select * from IN&n.VAPP22&i
%end;

%end;

[PaigeMiller]

The following code is untested.

%MACRO MERGED;
    PROC SQL;
        CREATE VIEW MERGE AS SELECT * FROM IN1.VAPP2120
        %do n=7 %to 33;
            %do i=1 %to 26;
                outer union corr select * from IN&n.VAPP22%sysfunc(putn(&i,z2.))
            %end;
        %end;
        ;
     quit;
%mend;
%merged

Please also note that the code is indented to increase readability; this is a good practice that will pay lots of benefits for you in the future. Also please note that there has to be a semi-colon at the end of the CREATE statement, and a quit; at the end of the PROC SQL code.

It may also be (I have never tried to do this) that this is too many merges to carry out in one SQL, and perhaps (again, I don't know) a DATA step merge would be more efficient.

[Antonio598]

The problem is that it created multiple combinations and I just need IN7.VAPP2201 then IN8.VAPP2202 and so forth. Also I need a period that it seems to be removed. 

ERROR: File WORK.IN7VAPP2201.DATA does not exist.
ERROR: File WORK.IN7VAPP2202.DATA does not exist.
ERROR: File WORK.IN7VAPP2203.DATA does not exist.
ERROR: File WORK.IN7VAPP2204.DATA does not exist.
ERROR: File WORK.IN7VAPP2205.DATA does not exist.

[PaigeMiller]

Okay, you wrote the %DO loops I was using, I tried to use them in my solution; if the looping in these %DO loops is not right, then please fix them. That includes the missing dot, please fix that as well.

Better yet, produce code without macros and without macro variables but hardcoding this merge for the first 3 data sets. Show us that code without macros and without macro variables.

[Quentin]

You need to add another dot.  When you code:

outer union corr select * from IN&n.VAPP22%sysfunc(putn(&i,z2.))

The dot after &n serves to end the macro variables reference.  This is necessary so that the macro processor does not look for a macro variable named nVAPP22.  That dot is part of the macro language, it is not generated SAS code.  So if you want to generate a SAS code dot, you need to add a dot.  i.e.:

outer union corr select * from IN&n..VAPP22%sysfunc(putn(&i,z2.))

With that, the first dot is a macro language dot, and the second dot is just text (i.e. generated SAS code).

Here's a log snip with a simple example:

1    %let lib=foo ;
2    %put &lib.bar ;
foobar
3    %put &lib..bar ;
foo.bar

[Antonio598]

The following worked as intended...Thank you all for your input...

%do i=01 %to 26;
%LET n= %EVAL(&i+7);

outer union corr select * from IN&n..VAPP22%sysfunc(putn(&i,z2))
%end;

[Patrick]

Your code sample indicates that the libraries are already assigned so below just for fun another coding option which could be useful in case you need access to different monthly tables.

/* create sample data */
filename codegen temp;
data _null_;
  file codegen;
/*  file print;*/
  put 'options dlcreatedir;';
  do i=1 to 7;
    put 
      '%let lr=in' i ';' /
      'libname &lr "%sysfunc(pathname(work))/&lr";' /
      'data &lr..vapp22' i z2. ';' /
      '  var="&lr";'/
      'run;' 
      ;
  end;
run;
%include codegen /source2;

/* create blank separated list of all source librefs in scope */
%let lrefs=;
proc sql noprint;
  select distinct libname into :lrefs separated by ' '
  from dictionary.libnames
  where prxmatch('/^IN\d+$/',strip(libname))>0
  ;
quit;

/* create concatenated library using all source librefs in scope */
libname in_all (&lrefs);

/* read one set of monthly source tables */
data work.want;
  set in_all.vapp22:;
run;

If the source libraries aren't already assigned then it wouldn't be hard to generate the concatenated library directly by generating the physical paths.

[ballardw]

Do you really have 20+ libraries named that way?

Part of your issue is that %do (and data step Do) variables do not have leading 0:

Run this:

%macro dummy;
%do i=01 %to 26;;
   %put i is: &i;
%end;
%mend;

%dummy

Creates

i is: 1
i is: 2
i is: 3
i is: 4
i is: 5
i is: 6
i is: 7
i is: 8
i is: 9
i is: 10
i is: 11
i is: 12
i is: 13
i is: 14
i is: 15
i is: 16
i is: 17
i is: 18
i is: 19
i is: 20
i is: 21
i is: 22
i is: 23
i is: 24
i is: 25
i is: 26

which means that VAPP22&i  resolves to VAPP221 not VAPP2201. So you get lots of data set doesn't exist. Not to mention the missing . you need before the set name to provide a dot in the resolved name because of the macro language use of &n.XXX means "resolve N then append the text XXX".