Error:Expecting a name

[rohithverma]

Hi All,

While executing the below query i have got an error as shared in the snapshot. Please let me know where the query had went wrong

QUERY:

PROC SQL;
SELECT COUNT(DISTINCT(AUTHORIZEDBY)) INTO: CNT FROM INTIAL;
SELECT DISTINCT(AUTHORIZEDBY) INTO : AUT1-: %SYSFUNC(COMPRESS(AUT&CNT)) FROM INTIAL;
QUIT;
%PUT &AUT1;
options MPRINT MLOGIC MERROR SYMBOLGEN;

%MACRO MAI;

%DO I=1 %TO 1;
PROC SQL;

CREATE TABLE FIRST AS
SELECT DISTINCT(CLOSED_ON),
COUNT(CASE WHEN compress(AUTHORIZEDBY)="&AUT&I." THEN 1 END)AS "&AUT&I."
FROM INTIAL
GROUP BY CLOSED_ON ;

QUIT;
%END;
%MEND;
%MAI;

[ballardw]

Please post LOG as text. Pictures are hard to copy/paste/ highlight or edit.

This line is pretty much syntactically incorrect as the error message indicates

COUNT(CASE WHEN compress(AUTHORIZEDBY)="&AUT&I." THEN 1 END)AS "&AUT&I."

The "as <something> " for a case expression has to be the name of a variable. Since SAS variable names cannot contain " characters that is error here.

Since you do not show where a macro variabl AUT is created or values assigned I think that you may have another logic issue even using &aut the way you have.

[rohithverma]

Please find the complete prog and log now

[Kurt_Bremser]

Rule #1 for macro development: start with WORKING non-macro code.

Get your SQL code to run for a single instance. Then proceed to make it dynamic.

[rohithverma]

I have already developed the manual code but i am struggling while creating a macro for this

[Kurt_Bremser]

---

@rohithverma wrote:
I have already developed the manual code but i am struggling while creating a macro for this

---

Post that code, so we can show you where you went wrong.

By the way: since you already have your data for automatizing in a dataset, all you need is a data _null_ step with call execute. No macro detour necessary.

[rohithverma]

i hv already posted complete code and log too

[Kurt_Bremser]

---

@rohithverma wrote:
i hv already posted complete code and log too

---

No. You only posted your failing attempt at macrotizing the code. I want you to show the non-macro code.

[Astounding]

&AUT&I. is always wrong.  The right version depends on seeing what the non-macro code should look like.  Two possibilities, either of which could be correct (and possibly one used in one place in the macro, and the other in another place):

AUT&I.

&&AUT&I.

[Tom]

If you want the value of AUT5 when I=5 then use 

&&AUT&I

If you want to name the variable AUTnnn where nnn is the value of the macro variable I then just use

AUT&i

without the quotes.  If you want PROC SQL to treat a quoted string in that location as a variable name then you need to add the DQUOTE=ANSI option to the PROC SQL statement.

Your initial SQL is working way too hard.

proc sql noprint;
  select distinct authorizedby into :aut1- from intial;
%let cnt=&sqlobs;
quit;

Your CASE statement logic does not match how you created the macro variables.  If you want to compare &AUT1 to the result of using the COMPRESS() function on AUTHORIZEDBY then you should include the COMPRESS() function in the code that generates the macro variables.  Might be better to just add the quotes into the macro variable when they are created. Include the TRIM() function to avoid storing the unneeded trailing spaces into the quoted string that is stored into the macro variables.

select distinct quote(trim(authorizedby)) into :aut1- from intial;
...
CASE WHEN AUTHORIZEDBY=&&AUT&I THEN 1 END