BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mgorripati
Obsidian | Level 7

Hi ,

 

Can someone help me understanding why One code does not work , but the other code with similar logic works ? I have also written the changes that makes difference

 

Not Working CODE :

 

 

%LET list=1000,2000;
options mlogic mprint symbolgen;
%macro Formulary ();
%let i=1;
%do %while (%qscan(%superq(list), &i, %str(, )) ne );
%let Formulary_ID=%qscan(%superq(list), &i, %str(, ));
%if &Formulary_ID=1000 %THEN
%if &Formulary_ID=2000 %THEN
%LET Summary=SASUSER.SUMMARY&Formulary_ID;
%macro report_summarize ();
PROC SQL;
CREATE TABLE &Summary AS
SELECT '1' as test1, '2' as test2
FROM &formulary
where ID=&Formulary_ID.
group by x ,y
having z not in ('6') ;
QUIT;
%mend report_summarize;

/* Call to Report Summarization Macro */

%report_summarize();
%let i=%eval(&i+1);
%end;
%mend Formulary;
%Formulary ();

 

Not Working Code LOG :

 

SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(FORMULARY): %DO %WHILE(%qscan(%superq(list), &i,  ) ne) loop beginning; condition is TRUE.
MLOGIC(FORMULARY): %LET (variable name is FORMULARY_ID)
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(FORMULARY): %IF condition &Formulary_ID=1000 is TRUE
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(FORMULARY): %IF condition &Formulary_ID=2000 is FALSE
MLOGIC(REPORT_SUMMARIZE): Beginning execution.


MPRINT(REPORT_SUMMARIZE): PROC SQL;
SYMBOLGEN: Macro variable SUMMARY resolves to SASUSER.SUMMARY&Formulary_ID
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "FORMULARY_ID".
51 SASUSER.SUMMARY1000
____
22
____
200
SYMBOLGEN: Macro variable FORMULARY resolves to SASUSER.Formulary
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(REPORT_SUMMARIZE): CREATE TABLE SASUSER.SUMMARY1000 AS SELECT '1' as test1, '2' as test2 FROM SASUSER.Formulary where
ID=1000 group by x ,y having z not in ('6') ;

ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(REPORT_SUMMARIZE): QUIT;
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

MLOGIC(REPORT_SUMMARIZE): Ending execution.

 

working code :

 

%LET list=1000,2000;
options mlogic mprint symbolgen;
%macro Formulary ();
%let i=1;
%do %while (%qscan(%superq(list), &i, %str(, )) ne );
%let Formulary_ID=%qscan(%superq(list), &i, %str(, ));
%if &Formulary_ID=1000 %THEN
%if &Formulary_ID=2000 %THEN
%LET Summary=SASUSER.SUMMARY&i;
%macro report_summarize ();
PROC SQL;
CREATE TABLE &Summary AS
SELECT '1' as test1, '2' as test2
FROM &formulary
where ID=&Formulary_ID.
group by x ,y
having z not in ('6') ;
QUIT;
%mend report_summarize;

/* Call to Report Summarization Macro */

%report_summarize();

%let i=%eval(&i+1);
%end;
%mend Formulary;
%Formulary ();

 

Working Code Log :

 

%Formulary ();
MLOGIC(FORMULARY): Beginning execution.
MLOGIC(FORMULARY): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(FORMULARY): %DO %WHILE(%qscan(%superq(list), &i,  ) ne) loop beginning; condition is TRUE.
MLOGIC(FORMULARY): %LET (variable name is FORMULARY_ID)
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(FORMULARY): %IF condition &Formulary_ID=1000 is TRUE
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(FORMULARY): %IF condition &Formulary_ID=2000 is FALSE
MLOGIC(REPORT_SUMMARIZE): Beginning execution.


MPRINT(REPORT_SUMMARIZE): PROC SQL;
SYMBOLGEN: Macro variable SUMMARY resolves to SASUSER.SUMMARY1
SYMBOLGEN: Macro variable FORMULARY resolves to SASUSER.Formulary
SYMBOLGEN: Macro variable FORMULARY_ID resolves to 1000
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(REPORT_SUMMARIZE): CREATE TABLE SASUSER.SUMMARY1 AS SELECT '1' as test1, '2' as test2 FROM SASUSER.Formulary where
ID=1000 group by group by x ,y having z not in ('6') ;
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING
clause of the associated table-expression referenced a summary function.
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table SASUSER.SUMMARY1 created, with 9150 rows and 2 columns.

MPRINT(REPORT_SUMMARIZE): QUIT;

 

 

Changes that made it work .

 

%LET Summary=SASUSER.SUMMARY&Formulary_ID;

 

to 

 

%LET Summary=SASUSER.SUMMARY&i;

 

Any insights on why the above code failed would be highly appreciated .

 

 

Thanks,

M

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

SQL is notorious for failing to unquote characters in time to correctly parse them.  I would try:

 

%let summary = %unquote(sasuser.summary&Formulary_id);

View solution in original post

7 REPLIES 7
Reeza
Super User

Rather than colour, you can use the little running man icon or {I} to include code. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, I am not going to look in depth at that code.  My first question is going to be why you are doing all that.  What is your source data and what do you want out?  Why, well a simple glance over the code - you are looping over a list of values.  Datastep language has do loops for this purpose, you then have all kinds of macro gubbins to arrive at a dataset name, why can this not be done in datastep?  Sasuser is not generally a good place to store things.  For the looping, you seem to be using a where clause with a group by - this does not work.  Also, why not just group by the loop group as well?  So, a good starting point is to post some test data - in the form of a datastep - with your starting data, and a breif image of what you want the output to look like.

 

Also to note, there are various typos in that code, for instance there are two %if %then statements that don't actually do anything:
%if &Formulary_ID=1000 %THEN
%if &Formulary_ID=2000 %THEN

mgorripati
Obsidian | Level 7

Hi RW9,

 

Thanks for your comments . My original code is over 1200 lines . I have only posted the code that is just enough to explain  the error.

 

My program generates excel files with metrics . 

 

I loop through each ID (1000,2000 etc..) and calculate metrics using proc sql and datastep with in a macro  for each ID and generate a excel report using PROC REPORT.

 

I hit a road block reading the macro varibles sepearated by comma and using them as a part of datasetname.

 

 

Astounding
PROC Star

SQL is notorious for failing to unquote characters in time to correctly parse them.  I would try:

 

%let summary = %unquote(sasuser.summary&Formulary_id);

mgorripati
Obsidian | Level 7

Thanks, that solves the issue. I need to learn a lot 🙂

Tom
Super User Tom
Super User

Looks to me like you are confusing the SAS parser with your macro quoting.  I would remove the macro quoting here since a valid value for a SAS dataset cannot include any characters that need macro quoting.

 

%LET Summary=%unquote(SASUSER.SUMMARY&Formulary_ID);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5609 views
  • 2 likes
  • 5 in conversation