topic Re: Parameter values as macro variables in SAS Enterprise Guide

Parameter values as macro variables

Ted — Wed, 22 Apr 2009 17:00:02 GMT

I'm trying to use some parameter values in code as macro variables. The values themselves are numbers but input as a string to show a date ie: 090313

This value is then needed to be used as part of a dataset name :

MYDATA_AREA_&MACRO..NAME FORMAT=$8

now the parameter value I'm inputting is being shown in the log OK but when the above code is reached it doesn't recognise the macro and I get the following:

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Does anyone have any ideas how to get this to work. The reason I'm using the parameters is so the user has a nice input box to use

Re: Parameter values as macro variables

Cynthia_sas — Wed, 22 Apr 2009 17:22:43 GMT

Hi:
I'm having a brain freeze trying to imagine how a dataset name is used with a FORMAT= option (possibly a PROC TABULATE statement?? but then I would expect the format to be a numeric format, not a character format):
[pre]
You show:
MYDATA_AREA_&MACRO..NAME format=$8
Which resolves to:
MYDATA_AREA_090313.NAME format=$8

Might expect:
proc tabulate data=MYDATA_AREA_&MACRO..NAME format=8.;
Resolves to:
proc tabulate data=MYDATA_AREA_090313.NAME format=8.;
[/pre]

Having a bit more code, such as a COMPLETE statement with the macro variable reference (or a bit of the SAS log error), might be aid debugging.

cynthia

Re: Parameter values as macro variables

Ted — Wed, 22 Apr 2009 18:35:12 GMT

This is taken from a Proc SQL statement I've had to change names etc due to the nature of the data. What you say it should resolve to is correct but it doesn't. This line is followed by about 20 more bringing in various variables and formatting them. I get the same error for everyone.

PROC SQL;
CREATE TABLE WORK.CUSTOMERS_NEW_ACCOUNT AS SELECT MYYDATA_AREA_&MACRO..ID FORMAT=$8.,
____
202

the 202 code is shown below the variable name.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,
>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,
LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

Re: Parameter values as macro variables

sbb — Wed, 22 Apr 2009 18:54:38 GMT

Suggest you explain your SAS log diagnostics perspective by adding the following line:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MPRINT;

Then don't always focus on the exact line that's raising the error condition - but also be cognizant of any comments and incorrectly (syntax) structured SAS code just prior to the error point.

Yours is typical - though without seeing the code just before the PROC SQL statement, it's a guess. Check for a SAS comment that is not terminated correctly, with a trailing asterisk. If that's not it, add the OPTIONS and rerun. Then if you can't figure it out, paste the more complete SAS Log, and include the SAS-generate logic that occurs just before the error message.

Scott Barry
SBBWorks, Inc.

Re: Parameter values as macro variables

Cynthia_sas — Wed, 22 Apr 2009 19:43:30 GMT

Hi:
Like Scott, I think it's possible that your error may be coming from some other location in your code. There are a few different ways to use a compound name to reference a particular column in a SELECT clause. The program below creates some test data and any of the methods work for me. I don't get any errors in the SAS log.

cynthia
[pre]
%let macro = 090313;
%put -----> resolves to: MYDATA_AREA_&macro;

ods listing;
data work.mydata_area_&macro;
set sashelp.class;
id = put(_n_,2.0);
run;

proc print data=work.mydata_area_&macro;
title "work.mydata_area_&macro";
run;

PROC SQL;
CREATE TABLE WORK.CUSTOMERS_NEW_ACCOUNT AS
SELECT MYDATA_AREA_&macro..ID FORMAT=$8.,
MYDATA_AREA_&macro..NAME,
MYDATA_AREA_&macro..AGE
FROM work.mydata_area_&macro;
quit;

proc print data=work.customers_new_account;
title 'after sql';
run;

** Alternate methods of referencing variables in PROC SQL;
PROC SQL;
CREATE TABLE WORK.ALT1 AS
SELECT ID FORMAT=$8.,
NAME,
AGE
FROM work.mydata_area_&macro;
quit;

PROC SQL;
CREATE TABLE WORK.ALT2 AS
SELECT AAA.ID FORMAT=$8.,
AAA.NAME,
AAA.AGE
FROM work.mydata_area_&macro as AAA;
quit;

[/pre]

Re: Parameter values as macro variables

Ted — Wed, 22 Apr 2009 19:47:46 GMT

OK so here's the actual log. The first part is pretty usual EG generated code.

;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Lib and intial Code);
4 %LET _EGTASKLABEL=%NRBQUOTE(Lib and intial Code);
5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(\\Dfz70162\199682030\workgroup\DMS99\42633-Auto Check PC New Claims\Live
5 ! SAS\Development Folder\AutoCheck_NEWACC_LiveV3Chop.egp);
6 %LET _SASPROGRAMFILE=;
7 %LET DLAC_DATE = %STR(090222);
8 %LET ICAC_DATE = %STR(090305);
9 %LET IICSC_DATE = %STR(090305);
10 %LET PCC_DATE = %STR(090313);
11 %LET PCC_MINUS1_DATE = %STR(090306);
12 %LET PCNC_DATE = %STR(090313);
13 %LET PSCSC_DATE = %STR(090305);
14
15 ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
16 OPTIONS DEV=ACTIVEX;
17 FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
18 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
18 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Enterprise%20Guide%204/BIClientStyles/EGDefault.css")
18 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
18 ! ;
19
20 %gaccessible;
PROC SQL;
64 CREATE TABLE WORK.PMC_CUSTOMERS_NEW_ACC AS SELECT PRIMARYDATA_NAMES_&PCC_DATE..IDNO FORMAT=$8.,
____
202

I ran with the option and got the following

35 PROC SQL;
36 CREATE TABLE WORK.PMC_CUSTOMERS_NEW_CLAIMS AS SELECT PRIMARYDATA_NAMES_&PCC_DATE..IDNO FORMAT=$8.,
SYMBOLGEN: Macro variable PCC_DATE resolves to 090313
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
36 CREATE TABLE WORK.PMC_CUSTOMERS_NEW_ACC AS SELECT PRIMARYDATA_NAMES_&PCC_DATE..IDNO FORMAT=$8.,
____
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Parameter values as macro variables

sbb — Wed, 22 Apr 2009 20:07:37 GMT

Unfortunately, blanks do not render well in pasted text, but if you notice your macro variable &PCC_DATE and what follows.

You have too many periods for your SAS variable name, I would submit - a period is not allowed:

PRIMARYDATA_NAMES_&PCC_DATE..IDNO

Scott Barry
SBBWorks, Inc.

Re: Parameter values as macro variables

Ted — Thu, 23 Apr 2009 09:59:08 GMT

I have all the periods in the right places. The only thing I can put it down to at the moment is the way the macro variable is used by EG from the intial input form.

By coding in the macro value without the use of the parameter form everything works fine.

Cynthia, I ran your code and it works fine but if I change the 'macro' to an EG parameter and then input its value via the parameter input screen I then get the same error I am experiencing with my own code. Message was edited by: Ted

Re: Parameter values as macro variables

Ted — Thu, 23 Apr 2009 11:19:25 GMT

It looks like I have finally solved the problem.

By using the %UNQUOTE macro on all the macro variables being created from the EG Parameter input form the code now works perfectly

%let PCC_DATE1 = %unquote(&PCC_DATE);

I guess this is a 'Feature' of EG : )

Thanks Cynthia and Scott for trying to help out.