Weird Macro resolving issue.

[VinnyR]

Hi, I am getting an error while passing the below code (Excerpts only). I have also shared the log (Below the  code).

ALL of the below code is in a Macro.

proc sql noprint;

create table frq02_01 as select count(distinct usubjid) as count, &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4

from frq01_01

group by &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4;

　

create table frq02_02 as select count(distinct usubjid) as count, &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4, atoxgr

from frq01_01

group by &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4, atoxgr;

create table frq02_03 as select count(distinct usubjid) as count, &byvar. &var. ord2, txt2, ord4, txt4

from frq01_01

group by &byvar. &var. ord2, txt2, ord4, txt4;

create table frq02_04 as select count(distinct usubjid) as count, &byvar. &var. ord2, txt2, ord4, txt4, atoxgr

from frq01_01

group by &byvar. &var. ord2, txt2, ord4, txt4, atoxgr;

quit;

Macro call:

%outputs(mpop=%str(upcase(isft3fl) = "Y"),pop=%str(upcase(parcat1) = "HEMATOLOGY"),

byvar=regionn, var=%str(,) ,output_name=h);

**********************************************LOG (Excerpts only) *********************************************;

MPRINT(OUTPUTS): proc sql noprint;

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

19646 ord2, txt2, ord3, txt3, ord4, txt4 from frq01_01 group by &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4;

____ ____

 The SAS System

22 201

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE,

GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 201-322: The option is not recognized and will be ignored.

19646 ! ord2, txt2, ord3, txt3, ord4, txt4 from frq01_01 group by &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4;

____

22

MPRINT(OUTPUTS): create table frq02_01 as select count(distinct usubjid) as count, regionn ,. ord2, txt2 , ord3, txt3, ord4, txt4 from frq01_01 group

by regionn , ord2, txt2, ord3, txt3, ord4, txt4;

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,

JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

MPRINT(OUTPUTS): create table frq02_02 as select count(distinct usubjid) as count, regionn , ord2, txt2, ord3, txt3, ord4, txt4, atoxgr from frq01_01

group by regionn , ord2, txt2, ord3, txt3, ord4, txt4, atoxgr;

NOTE: Statement not executed due to NOEXEC option.

MPRINT(OUTPUTS): create table frq02_03 as select count(distinct usubjid) as count, regionn , ord2, txt2, ord4, txt4 from frq01_01 group by regionn ,

ord2, txt2, ord4, txt4;

NOTE: Statement not executed due to NOEXEC option.

MPRINT(OUTPUTS): create table frq02_04 as select count(distinct usubjid) as count, regionn , ord2, txt2, ord4, txt4, atoxgr from frq01_01 group by

regionn , ord2, txt2, ord4, txt4, atoxgr;

NOTE: Statement not executed due to NOEXEC option.

MPRINT(OUTPUTS): quit;

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

NOTE: PROCEDURE SQL used (Total process time):

So from all what I understand is that the issue is in the first Proc SQL Statement and that the macro variable VAR is not resolving properly (as a comma ","). Could anybody shed some light. Any help is greatly appreciated.

[ali_jooan]

you have to use comma to between different variables in SELECT and GROUP BY statements.

select count(distinct usubjid) as count, &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4

from frq01_01

group by &byvar. &var. ord2, txt2, ord3, txt3, ord4, txt4;

So put comma between &byvar. , &var. , ord2 and if everything else is ok, your code should run

[ShiroAmada]

Try this

%put Byvar is: &byvar.; 
%put Var is: &var.;

Check the log.

If the value of &byvar. &var. 

does not contain a comma (,) then modify your script like this

proc sql noprint;
create table frq02_01 as select count(distinct usubjid) as count, &byvar., &var., ord2, txt2, ord3, txt3, ord4, txt4
from frq01_01
group by &byvar., &var., ord2, txt2, ord3, txt3, ord4, txt4;
　
create table frq02_02 as select count(distinct usubjid) as count, &byvar., &var., ord2, txt2, ord3, txt3, ord4, txt4, atoxgr
from frq01_01

group by &byvar., &var., ord2, txt2, ord3, txt3, ord4, txt4, atoxgr;
create table frq02_03 as select count(distinct usubjid) as count, &byvar., &var., ord2, txt2, ord4, txt4
from frq01_01

group by &byvar.,&var., ord2, txt2, ord4, txt4;
create table frq02_04 as select count(distinct usubjid) as count, &byvar., &var., ord2, txt2, ord4, txt4, atoxgr
from frq01_01
group by &byvar., &var., ord2, txt2, ord4, txt4, atoxgr;
quit;

Hope this helps.

[Tom]

Let's re arrange your SQL statement to make it a little easier for humans to scan.

create table frq02_01 as 
select count(distinct usubjid) as count
     , &byvar. &var. ord2
     , txt2
     , ord3
     , txt3
     , ord4
     , txt4
from frq01_01
group by &byvar. &var. ord2
       , txt2 
       , ord3 
       , txt3 
       , ord4 
       , txt4
;

The first thing I notice is that you have no commas between &BYVAR and &VAR or between &VAR and ORD2. 

Assuming that the macro code not shown hasn't done anything to change the values of BYVAR and VAR this means that if you pass in any values in those parameters then you will need to include both embedded commas and also add a trailing comma. 

Now let's look at how you are setting this parameters.

%outputs
(mpop=%str(upcase(isft3fl) = "Y")
,pop=%str(upcase(parcat1) = "HEMATOLOGY")
,byvar=regionn
,var=%str(,) 
,output_name=h
);

Looks a little fishy.  I would have added the comma to the BYVAR value and left VAR as blank.  But combined effect should work as your variable list should look like:

select count(distinct usubjid) as count
     , regionn , ord2
     , txt2
     , ord3
     , txt3
     , ord4
     , txt4

So if SAS is complaining then it is probably because of the macro quoting you had to add to the comma to get it to pass through the macro call.  You could just remove the macro quoting. You could do it where you reference the macro variables.

     , %unquote(&byvar. &var.) ord2

Or perhaps since you appear to be referencing the macro variables in multiple places you might want to just do it at the top of the macro.

%let byvar=%unquote(&byvar);
%let var=%unquote(&var);