error 22-322: syntax error, expecting one of the following: a quoted string, ',', as, form

[babykxy]

I am new in sas area, I run below code got error message as below, please help me figure out that, thanks,

NOTE: Line generated by the invoked macro "LOOP".

7 select count (distinct presc_ord_npi) as &value1 from bhrx_presc as a where

----------

22

7 ! gnrc_name like "&value"; quit;

ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.

code:

%macro loop(values);   
       %let count=%sysfunc(countw(&values));                                                                                       
     %do i = 1 %to &count;                                                                                                             
      %let value=%qscan(&values,&i,%str(,));  

%let value=%sysfunc(dequote(&value));
  %put &value;
    %let value1= %qscan(&value,1);
   proc sql;
select count (distinct presc_ord_npi) as &value1  from bhrx_presc as a where gnrc_name like "&value";
      quit;
     %end;                                                                                                                                                                                                            %mend;                                                                                                                                        
                                                                    
%loop(%str('METHYLPHENIDATE HCL','DEXTROAMPHETAMINE/AMPHETAMINE,CLONIDINE HCL'));

[Astounding]

It's not clear what you would like VALUE1 to contain.  But it is clear that the problem lies  in that area.  You are getting unbalanced single quotes within the value for VALUE1, since %SCAN does not use quotes as delimiters.  What are there any quotes at all in your original list?

[babykxy]

thank you for you help!!!

VALUE1 is for result part I can get count name as VALUE1,

ex:

The SAS System

METHYLPHENIDATE

7879

there are not quotes l in my original list, but even I remove quotes from list still got same error: 

NOTE: Line generated by the invoked macro "LOOP".

11 select count (distinct presc_ord_npi) as &value1 from bhrx_presc as a

----------

22

11 ! where gnrc_name like "&value"; quit;

ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

code:

%macro loop(values);                                                                                                                                      
     %let count=%sysfunc(countw(&values));                                                                                       
     %do i = 1 %to &count;                                                                                                             
      %let value=%qscan(&values,&i,%str(,)); 
  %put &value;
    %let value1= %qscan(&value,1);
   proc sql;
select count (distinct presc_ord_npi) as &value1  from bhrx_presc as a where gnrc_name like "&value";
      quit;
     %end;                                                                                                                                                                                                                                                                   
%mend;                                                                                                                                                                                                            
%loop(%str(METHYLPHENIDATE HCL,DEXTROAMPHETAMINE/AMPHETAMINE,CLONIDINE HCL));

[novinosrin]

your macro call has unmatched quotes in your parameters

%loop(%str('METHYLPHENIDATE HCL','DEXTROAMPHETAMINE/AMPHETAMINE,CLONIDINE HCL'));

should be:

%loop(%str('METHYLPHENIDATE HCL','DEXTROAMPHETAMINE/AMPHETAMINE','CLONIDINE HCL'))

Also, I don't see any reason in your code to have the quotes in your parameter in the first place, then quote the scanned result with a qscan and then dequote and then call in your proc sql. 

[babykxy]

thank you so much!

you are right! I remove quote from list, also dequote statement, but program still have same error message!

%macro loop(values);                                                                                                                                      
     %let count=%sysfunc(countw(&values));                                                                                       
     %do i = 1 %to &count;                                                                                                             
      %let value=%qscan(&values,&i,%str(,)); 
  %put &value;
    %let value1= %qscan(&value,1);
   proc sql;
select count (distinct presc_ord_npi) as &value1  from bhrx_presc as a where gnrc_name like "&value";
      quit;
     %end;                                                                                                                                                                                                                                                                    
%mend;                                                                                                                                        
%loop(%str(METHYLPHENIDATE HCL,DEXTROAMPHETAMINE/AMPHETAMINE,CLONIDINE HCL));

NOTE: Line generated by the invoked macro "LOOP".

11 select count (distinct presc_ord_npi) as &value1 from bhrx_presc as a

                                                                                             ----------

                                                                                                22

11 ! where gnrc_name like "&value"; quit;

ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

[novinosrin · Accepted solution]

Ok, now you really made me read your code. 

The problem is in your count, Try the below correction and let me know if that works

%macro loop(values);
%let count=%sysfunc(countw(&values,%str(,)));
%put c=&count;
%do i = 1 %to &count;
%let value=%qscan(&values,&i,%str(,));

%let value1= %qscan(&value,1);
%put value1=&value1;
proc sql ;
select count (distinct presc_ord_npi) as &value1 from bhrx_presc as a
where gnrc_name like "&value";
quit;
%end;
%mend;
%loop(%str(METHYLPHENIDATE HCL,DEXTROAMPHETAMINE/AMPHETAMINE,CLONIDINE HCL));

[babykxy]

thank you so much!!! it's works!

[babykxy]

Thank you so much!!! it's works now!

[novinosrin]

Ok good I am glad. @babykxy May i request you to mark the question as answered and close the thread if you don't mind plz