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
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
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.
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.
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.
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
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.
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:
%macro dummy; %do i=01 %to 26;; %put i is: &i; %end; %mend; %dummy
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".
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.