using Macro variable to create dataset name does not work

mgorripati — Fri, 12 Aug 2016 14:24:53 GMT

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;

%LET Summary=SASUSER.SUMMARY&i;

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

Thanks,

Re: using Macro variable to create dataset name does not work

Reeza — Fri, 12 Aug 2016 14:24:31 GMT

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

Re: using Macro variable to create dataset name does not work

RW9 — Fri, 12 Aug 2016 14:42:33 GMT

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

Re: using Macro variable to create dataset name does not work

Astounding — Fri, 12 Aug 2016 14:50:42 GMT

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

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

Re: using Macro variable to create dataset name does not work

mgorripati — Fri, 12 Aug 2016 14:52:35 GMT

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.

Re: using Macro variable to create dataset name does not work

Tom — Fri, 12 Aug 2016 14:58:56 GMT

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);

Re: using Macro variable to create dataset name does not work

mgorripati — Fri, 12 Aug 2016 15:11:00 GMT

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

Re: using Macro variable to create dataset name does not work

mgorripati — Fri, 12 Aug 2016 15:11:28 GMT

thanks tom.

topic using Macro variable to create dataset name does not work in SAS Programming

using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work

Re: using Macro variable to create dataset name does not work