PROC SQL inside MACRO

[rfrancis]

Hi everyone, there is an obvious (to someone other than myself) syntax problem with the code below (SAS 9.4)  that will not allow SAS to recognize the filename "predict_2_I&&J (error is file does not exist). Both files are created within the macro. Could someone please point out the syntax or other failure in my code below?  Thank you, Rick

proc sql;
    create table &ts_fcst_&I&J_ as
       select * from
       ts_2_&I&J as a inner join predict_2&I&J as b on
       a.group=b.group and a.cvrank=b.cvrank;

[Reeza]

Try adding the period to indicate the end of a macro variable? Note that I can't tell if your macro variable is ts or ts_fcst or ts_fcst_1_3 with i=3 and j=3......and neither can the compiler.

table &ts._fcst_&I.&J._

Have you also ran your code with the debugging options on to see how the compiler is seeing it?

options mprint symbolgen;

---

@rfrancis wrote:

Hi everyone, there is an obvious (to someone other than myself) syntax problem with the code below (SAS 9.4)  that will not allow SAS to recognize the filename "predict_2_I&&J (error is file does not exist). Both files are created within the macro. Could someone please point out the syntax or other failure in my code below?  Thank you, Rick

proc sql;
    create table &ts_fcst_&I&J_ as
       select * from
       ts_2_&I&J as a inner join predict_2&I&J as b on
       a.group=b.group and a.cvrank=b.cvrank;

---

[rfrancis]

Thanks Reeza, see what I can do.

[DavePrinsloo · Accepted solution]

Could you post more of the macro which to at least show the macro variable definitions before the proc sql step,
When you say filename, do you mean table name?
It is a good habit to use a period after macro variable names when you use them.
Use OPTIONS MPRINT to see exactly the code that is generated.

[rfrancis]

Hi Dave, included the full macro below. There are no macro variable definitions, always worked without them, not sure why they are needed now (not saying they are not needed, just lack the necessary knowledge for their inclusion). The primary error is "File Predict_2 does not exist." if this forum uses tags to identify code, I am happy to oblige, just have no knowledge for the tags. I apologize. Thank you for your interest! Rick

options mprint symbolgen;

%MACRO combination;

*option spool;

data untrimmed1_&I&J;
set untrimmed1;
if group=&I and lagcvrank eq &J; run;

proc surveyselect data=untrimmed1_&I&J method=srs n=2 seed=3965
out=comb_2 rep=1 noprint; quit;

filename templog dummy;
*proc printto log=templog; run;

run;

proc reg data=comb_2 noprint adjrsq outest=ts_2_est_&I&J
(keep= group replicate lagcfo lagcfo_cv lagcvrank);
by group lagcvrank replicate;
model cfo = lagcfo lagcfo_cv / noint noprint adjrsq;
output out=predict_2_&I&J; quit;
run;

data ts_2_est_&I&J;
set ts_2_est_&I&J;
tse_2a=lagcfo;
tse_2b=lagcfo_cv;

run;

data int&I&J;
set predict_2;
int_raw=cfo-predict2;

proc means data=ts_2_est_&I&J noprint; format ts_2a ts_2b 12.6;
by group lagcvrank;
var tse_2a tse_2b;
output out=ts_2_&I&J (drop=_TYPE_ _FREQ_) median(tse_2a)=ts_2a
median(tse_2b)=ts_2b;
quit;

proc means data=int&I&J noprint;
by group lagcvrank;
var int_raw;
output out=med_int_&I&J (drop=_TYPE_ _FREQ_) median(int_raw)=int;

run;

proc sql;
create table ts_fcst_&I&J_ as
select * from
ts_2_&I.&J. as a inner join predict_2_&I&J as b on
a.group=b.group and a.cvrank=b.cvrank;

proc sql;
create table ts_fcst_&I&J as
select * from
ts_fcst_&I&J_ as a inner join med_int_&I&J on
a.group=b.group and a.cvrank=b.cvrank;

*proc printto; run;

%MEND combination;
%MACRO LOOP;
%DO I=1994 %TO 2018;
%DO J=0 %TO 4;
%combination;
%END;
%END;
%MEND LOOP; %LOOP;

[rfrancis]

Hi Dave, corrected a few mistakes, added a few tweaks, seems to run now. FWIW, Included the working version below.. Sorry to waste your time. Rick

options mprint symbolgen;

%MACRO combination;

*option spool;

data untrimmed1_&I&J;
set untrimmed1;
if group=&I and lagcvrank eq &J; run;

proc surveyselect data=untrimmed1_&I&J method=srs n=2 seed=3965
out=comb_2 rep=1 noprint; quit;

filename templog dummy;
*proc printto log=templog; run;

run;

proc reg data=comb_2 noprint adjrsq outest=ts_2_est_&I&J
(keep= group replicate lagcfo lagcfo_cv lagcvrank);
by group lagcvrank replicate;
model cfo = lagcfo lagcfo_cv / noint noprint adjrsq;
output out=predict_2_&I&J; quit;
run;

data ts_2_est_&I&J;
set ts_2_est_&I&J;
tse_2a=lagcfo;
tse_2b=lagcfo_cv;

run;

data int&I&J;
set predict_2_&i&J;
*int_raw=cfo-predict2;

proc means data=ts_2_est_&I&J noprint; format ts_2a ts_2b 12.6;
by group lagcvrank;
var tse_2a tse_2b;
output out=ts_2_&I&J (drop=_TYPE_ _FREQ_) median(tse_2a)=ts_2a
median(tse_2b)=ts_2b;
quit;

*proc means data=int&I&J noprint;
*by group lagcvrank;
*var int_raw;
*output out=med_int_&I&J (drop=_TYPE_ _FREQ_) median(int_raw)=int;

run;

proc sql;
create table ts_fcst_&I&J._ as
select * from
ts_2_&I&J as a inner join predict_2_&I&J as b on
a.group=b.group and a.lagcvrank=b.lagcvrank;

*proc sql;
*create table &ts_fcst_&I&J as
*select * from
*ts_fcst_&I&J_ as a inner join med_int_&I&J on
*a.group.=b.group. and a.cvrank.=b.cvrank.;

*proc printto; run;

%MEND combination;
%MACRO LOOP;
%DO I=1994 %TO 2018;
%DO J=0 %TO 4;
%combination;
%END;
%END;
%MEND LOOP; %LOOP;

[ballardw]

What are the values of &i and &j?

Also your question text uses: "predict_2_I&&J." which is indeed a very different thing than "predict_2_&I&J

If you actually have &&J when the macro processor sees two & it "holds" the first one then resolves &J. The "uses" the held & to resolve.

So if &j is CAT then &&J =>&Cat. Which would thrown an error if you do not have a macro variable &Cat defined.

HInt: when you get an error copy the proc or data step code will all the notes and errors from the log and paste into a code box opened on the forum with the </> icon.
The code box is important to keep the message windows from reformatting text and moving the diagnostic characters that frequently appear with error messages.

[rfrancis]

Hello, I'm sensitive to the need for a code box. However, I see no such icon anywhere on the page. Sorry.

[Tom]

Here they are:

[rfrancis]

Thank you!!