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'));
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));
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?
thank you for you help!!!
VALUE1 is for result part I can get count name as VALUE1,
ex:
The SAS System |
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));
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.
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
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));
thank you so much!!! it's works!
Thank you so much!!! it's works now!
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.