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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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.

dhana
Fluorite | Level 6

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

Patrick
Opal | Level 21

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

dhana
Fluorite | Level 6

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

Reagrds

Dhana

Reeza
Super User

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#a0...

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;

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

%put &ACCT_NBR_D.;

%put &ACCT_NBR_M.;

%MEND FORMAT_DATASET;

%put &ACCT_NBR_D.;

%put &ACCT_NBR_M.;

Tom
Super User Tom
Super User

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;

dhana
Fluorite | Level 6

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

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
  • 17156 views
  • 3 likes
  • 4 in conversation