proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS not

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS not

 

Hi All,

 

If I run the following part of code with proc SQL it is working fine :

 

 

data temppcm1 (keep=TIME Schm1);   set pcmout_test (where=(Schm1  ^=.));    run;                                                          

                                                                                                                                       

proc sql noprint;                                                                                                                       

                                                                                                                                       

select nlobs into :nobs  from dictionary.tables where libname='WORK' and memname='TEMPPCM1'  ; 

QUIT;                                 

                                                                                                                                       

data TEMPpcm1    ; set TEMPpcm1;                                                                                                      

                                                                                                                                       

if    time > &nobs -12 then   Schm1=.;                                                                                                 

                                                                                                                                       

run;                                                                                                                                    

                                                                                                                                       

data pcm1 (keep= Schm1);  set temppcm1 (where=(Schm1 ^=.)); run;     

 

 

However , if I want to embed into a macro and want to the same exercise for 13 such variables(SCHM1--SCMH`13)  from the file  pcmout_test (ATTACHED A SAMPLE OF THE DATA, it is of same structure) ,  then it is throwing error    .                                                           

 CODE:

%MACRO M1;                                                                                                                             

                                                                                                                                   

                                                                                                                                     

%DO I = 1 %TO 13;                                                                                                                       

                                                                                                                                       

data temppcm&I (keep=TIME Schm&I);   set pcmout_test (where=(Schm&I ^=.));                                                              

                                                                                                                                       

proc sql noprint;                                                                                                                       

                                                                                                                                       

select nlobs into :nobs  from dictionary.tables where libname='WORK' and memname='TEMPPCM&I'  ;  QUIT;                                 

                                                                                                                                       

data TEMPpcm&I    ; set TEMPpcm&I;                                                                                                      

                                                                                                                                       

if    time >  &nobs -12 then   Schm&I=.;                                                                                                 

                                                                                                                                       

run;                                                                                                                                    

                                                                                                                                       

data pcm&I (keep= Schm&I);  set temppcm&I (where=(Schm&I ^=.)); run;                                                                   

                                                                                                                                        

                                                                                                                                       

%END;                                                                                                                                   

                                                                                                                                                                                                                                     

%MEND M1;                                                                                                                               

                                                                                                                                       

 %M1 run;                   

 

LOG showing the error:

 

LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and

              COLUMN where the error has occurred.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

              a numeric constant, a datetime constant, a missing value, bitstring, INPUT, PUT.

WARNING: Apparent symbolic reference NOBS not resolved.

                                                                                                           

     Any help?                                                                                                                                  

                                                                                                                                        


Accepted Solutions
Solution
‎10-31-2016 01:04 PM
SAS Super FREQ
Posts: 683

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

The problem lies in this line

where libname='WORK' and memname='TEMPPCM&I'  

For a Macro Trigger to resolve, it must be within double quotes, like so

where libname='WORK' and memname= "TEMPPCM&I"  

Bruno

View solution in original post


All Replies
PROC Star
Posts: 552

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

What version of SAS are you running?

 

And why are you writing 'run' after your macro call?

New Contributor
Posts: 3

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

9.2



Makes no difference




Solution
‎10-31-2016 01:04 PM
SAS Super FREQ
Posts: 683

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

The problem lies in this line

where libname='WORK' and memname='TEMPPCM&I'  

For a Macro Trigger to resolve, it must be within double quotes, like so

where libname='WORK' and memname= "TEMPPCM&I"  

Bruno

Super User
Super User
Posts: 7,403

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

Hi,

 

Sorry, I am not downloading xls files, post test data in the form of a datastep within the post.  As for your question, I don't understanda why you need to repeat the code the 13 times?  If you have a set of varaibles all with the same prefix, then the programming methodology to access these is by arrays.  Also, I do not understand why number of observations in the dataset should be an indicator of time.  If the data is sorted in any other way the results will be totally different.  Please clarify what your input data is, and what you want out as one simple datastep with and array statement should suffice:

data temppcm1 (keep=TIME Schm:);
  set pcmout_test;
  array schm:;
  do over schm;
    if <logic> then schm=.;
  end;
run; 
Super User
Posts: 10,500

Re: proc sql working fine but not working when embabed in a macro :Apparent symbolic reference NOBS

For future questions involving errors please post the log with the code and the error message. If you post into one of the boxes by clicking on the "run" icon or the "{i}" in the menu above you can maintain the format of the log. The SAS errors often include an underscore character with the text indicating where the specific message thinks the error occurs.

 

If you are using a macros then run the macro with macro options such as:

options MPRINT symbolgen;

%M1

 

so that the code generated by the macro is visible and likely places the error into context.

 

The 'RUN;' AFTER your macro call is likely an error generator as the data steps had their run statements inside the macro.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 496 views
  • 2 likes
  • 5 in conversation