BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rfrancis
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
DavePrinsloo
Pyrite | Level 9
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.


View solution in original post

9 REPLIES 9
Reeza
Super User

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
Obsidian | Level 7
Thanks Reeza, see what I can do.
DavePrinsloo
Pyrite | Level 9
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
Obsidian | Level 7
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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
Hello, I'm sensitive to the need for a code box. However, I see no such icon anywhere on the page. Sorry.
Tom
Super User Tom
Super User

Here they are:

image.png

rfrancis
Obsidian | Level 7

Thank you!! 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 820 views
  • 1 like
  • 5 in conversation