topic Re: Macro keyword LET appears as text in SAS Programming

Macro keyword LET appears as text

dhana — Fri, 06 Jan 2012 21:00:57 GMT

Hi All,

I am in the process of setting up auto call macro library for my project. As part of that I have created macro module which will read the dataset and format the account number present in the dataset and store it in two different variable ACCT_NBR_M ($18.) and ACCT_NBR_D ($22.). Actually these two formats are to used to query different tables.

Finally I am putting all the account number into a single macro variable, which I can use it in my where condition like this where account_num in (&acct_nbr_m);

Below is the code I am using to do the above step.

%MACRO FORMAT_DATASET(LIB=,TABLE=);

PROC DATASETS LIBRARY=&LIB;
DELETE &TABLE;
RUN;

DATA &LIB..LISTFILE(DROP=ACCT_NBR);
SET &LIB..&TABLE(KEEP=ACCT_NBR);
FORMAT ACCT_NBR_MQ $QUOTE20. ACCT_NBR_DQ $QUOTE24.;
ACCT_NBR_MQ=PUT(INPUT(ACCT_NBR,22.),Z18.);
ACCT_NBR_DQ=PUT(INPUT(ACCT_NBR,22.),Z22.);
RUN;

PROC SQL;
SELECT ACCT_NBR_DQ INTO :ACCT_NBR_DQ SEPARATED BY "," FROM &LIB..LISTFILE;
SELECT ACCT_NBR_MQ INTO :ACCT_NBR_MQ SEPARATED BY "," FROM &LIB..LISTFILE;
QUIT;

%LET ACCT_NBR_D=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_DQ),%BQUOTE("),%BQUOTE(')));
%LET ACCT_NBR_M=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_MQ),%BQUOTE("),%BQUOTE(')));

%MEND FORMAT_DATASET;

The code works perfectly with out putting them inside macro, but when I placed them inside the %macro %mend and try to access them from some other location, I am getting the following error.

ERROR: Macro keyword LET appears as text. A semicolon or other delimiter may be missing.

Am I missing anything when I place the code inside the %macro and %mend. Please help.

In the same program I have another question, the macro variable ACCT_NBR_M is not holding more than 1500+ accounts. Do we have any option in SAS to expand the macro variable storage length.

Thanks

Dhanasekaran R

Macro keyword LET appears as text

Tom — Fri, 06 Jan 2012 21:13:28 GMT

I do not see any statement declaring ACCT_NBR_D and ACCT_NBR_M as global or local macro variables.

This means that your macro will work differently depending if those macro variables have been defined before the macro is called.

If they have not been defined then they will be made local and will disappear when %FORMAT_DATASET macro exists.

If they have been defined then the macro will change their values.

Re: Macro keyword LET appears as text

dhana — Fri, 06 Jan 2012 21:50:58 GMT

You mean to say that in the program where I am calling this macro %FORMAT_DATASET, I should declare %GLOBAL ACCT_NBR_D;

%GLOBAL ACCT_NBR_M;

But the Error message looks to be a problem with the %LET statement.

Thanks

Dhanasekaran R

Re: Macro keyword LET appears as text

Reeza — Fri, 06 Jan 2012 22:21:59 GMT

Macrovariable values have a maximum length of 65,534 characters, your 1500 accounts are using up all that space and there is no option to increase that size.

Since you define the macro variables within the macro then they disappear when that macro is finished and you can't call them outside of the macro.

Use the %global statement to define the macro.

The ERROR message is an indicator, you have shown how you're calling the macro variables so we can't tell you if you have another error or not.

Here some info on macro variable scope:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001072111.htm#a001072130

Check the scope of your variables by using some %put statements inside your macro and outside.

HTH

%MACRO FORMAT_DATASET(LIB=,TABLE=);

PROC DATASETS LIBRARY=&LIB;
DELETE &TABLE;
RUN;

PROC SQL;
SELECT ACCT_NBR_DQ INTO :ACCT_NBR_DQ SEPARATED BY "," FROM &LIB..LISTFILE;
SELECT ACCT_NBR_MQ INTO :ACCT_NBR_MQ SEPARATED BY "," FROM &LIB..LISTFILE;
QUIT;

%LET ACCT_NBR_D=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_DQ),%BQUOTE("),%BQUOTE(')));
%LET ACCT_NBR_M=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_MQ),%BQUOTE("),%BQUOTE(')));

%put &ACCT_NBR_D.;

%put &ACCT_NBR_M.;

%MEND FORMAT_DATASET;

%put &ACCT_NBR_D.;

%put &ACCT_NBR_M.;

Re: Macro keyword LET appears as text

Patrick — Fri, 06 Jan 2012 22:28:04 GMT

I could replicate what you describe and I believe what you observe is caused by incorrect quoting of unmatched quotation marks. You need to use a % in front of the quotation mark like documented:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001061290.htm

I would use %str() so your statements would look like:

%LET ACCT_NBR_D=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_DQ),%str(%"),%str(%')));

%LET ACCT_NBR_M=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_MQ),%str(%"),%str(%')));

Macro vars store up to 32K (or 64K on 64bit OS - not sure if true for all 64bit OS).

Re: Macro keyword LET appears as text

Tom — Sat, 07 Jan 2012 16:29:10 GMT

You have received help on the issues of generating single quote characters that should help a little with your problem.

Your largest issue is the one you listed last. There is a limit to the number of characters that can be stored in a macro variable.

So you need to look at your process and decide how you want to handle that. In general you might want to modify your process to use a dataset instead of a macro variable to pass the list of account numbers. Of course this might cause other trouble, for example look at this thread on subsetting data from Teradata tables. (http://communities.sas.com/message/112731#112731)

Your macro also has some other logic and coding errors.

1) You delete the input dataset before you use it.

2) You are defining character variables that are two spaces longer than you need. This is because the first reference to the variables is the format statement. So it uses the length of the format for the length of the variables. You could add a length statement before the format statement to fix that.

3) You could eliminate your problem with the macro quoting of single quotes by just doing it in basic SAS statements instead. For example you could build the macro variables directly from the input numeric variable.

PROC SQL noprint;
  SELECT "'"||put(ACCT_NBR,z18.)||"'"
       , "'"||put(ACCT_NBR,z22.)||"'"
    INTO :ACCT_NBR_DQ SEPARATED BY "," 
       , :ACCT_NBR_MQ SEPARATED BY "," 
    FROM &LIB..&TABLE
  ;
QUIT;

4) Because your strings are fixed length it would be easy to test if your query would generate a macro variable that is too long.

%if %sysevalf( &sqlobs*25 > 65534) %then %do;

%put ERROR: Too many accounts selected to for macro variable generation.

%end;

Re: Macro keyword LET appears as text

dhana — Tue, 10 Jan 2012 19:04:37 GMT

Thanks a lot Tom...! Actually I noticed and corrected couple of errors that you have mentioned here while I was running the program.

I have learned a new way of creating account numbers with quotes, thanks a lot for that.

I will test this and get back to you.

Thanks

Dhana

Re: Macro keyword LET appears as text

dhana — Tue, 10 Jan 2012 19:05:51 GMT

Thanks Patrick...! This actually solved my probelm.

Reagrds

Dhana