topic Re: PROC SQL inside MACRO in SAS Programming

PROC SQL inside MACRO

rfrancis — Wed, 08 Apr 2020 21:29:06 GMT

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;

Re: PROC SQL inside MACRO

Reeza — Wed, 08 Apr 2020 21:41:48 GMT

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;

Re: PROC SQL inside MACRO

rfrancis — Wed, 08 Apr 2020 21:46:49 GMT

Thanks Reeza, see what I can do.

Re: PROC SQL inside MACRO

DavePrinsloo — Wed, 08 Apr 2020 21:47:52 GMT

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.

Re: PROC SQL inside MACRO

ballardw — Wed, 08 Apr 2020 21:49:19 GMT

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.

Re: PROC SQL inside MACRO

rfrancis — Wed, 08 Apr 2020 22:45:01 GMT

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

Re: PROC SQL inside MACRO

rfrancis — Wed, 08 Apr 2020 23:26:24 GMT

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;

Re: PROC SQL inside MACRO

rfrancis — Wed, 08 Apr 2020 23:52:11 GMT

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;

Re: PROC SQL inside MACRO

Tom — Thu, 09 Apr 2020 01:32:36 GMT

Here they are:

Re: PROC SQL inside MACRO

rfrancis — Thu, 09 Apr 2020 17:01:46 GMT

Thank you!!