ERROR: More positional parameters found than defined

[brighterlight]

Hi everyone,

would appreciate your help in this. This code is the simplified version of the original code, I have tried using '%macro SAMPLE:' it still did not work, I could not figure out what went wrong in this code as it has always been able to run until a couple of days back. Thanks in advance your help.

%MACRO SAMPLE();

PROC SQL;
CREATE TABLE ALL_KEYS AS
SELECT DISTINCT KEYS, ROOM
FROM ALL_DATA;
QUIT;

PROC SQL;
CREATE TABLE ALL_KEYS AS SELECT * , MONOTONIC() AS SEQ FROM ALL_KEYS;
QUIT;

%let l = 1;
%let keycount = %obscnt(ALL_KEYS);

%do %while(&l le &keycount);

PROC SQL NOPRINT;
SELECT COMPRESS(KEYS), ROOM INTO:KEY,:ROOM FROM ALL_KEYS WHERE SEQ = &l.;
quit;
%let ROOM = %TRIM(&ROOM.);
%let KEY = %TRIM(&KEY);

PROC SQL;
CREATE TABLE DATA_&KEY. AS SELECT
PUT(DATEPART('CHECK IN DATE'n), EURDFDE7.) AS DATE,
PUT(TIMEPART('CHECK IN TIME'n), TIME6.2) AS TIME,
CAT("'",CUSTOMERNO) AS CUSTOMERNO

FROM ALL_DATA;
QUIT;

%let l = %eval(&l + 1);
%end;
%mend SAMPLE;
%SAMPLE;

[PaigeMiller]

It would help if you showed us the relevant parts of the SASLOG. It would help to know where in the code the problem is showing up.

Turn on OPTIONS MPRINT; at the start of your program, before you run this macro.

Then, here in the SAS Communities, click on the {i} icon and paste the relevant parts your SASLOG in. Do not paste the SASLOG in if you don't click on {i}

[Kurt_Bremser]

Step 1: make your code readable. Use lowercase, and some visual formatting so that functional blocks are recognizable.

If I wrote such ugly messes, I'd never get finished with jobs.

%macro sample;

proc sql;
create table all_keys as
select distinct
  keys,
  room
from all_data;
quit;

proc sql;
create table all_keys as
select
  *,
  monotonic() as seq
from all_keys;
quit;

%let l = 1;
%let keycount = %obscnt(all_keys);

%do %while(&l le &keycount);

  proc sql noprint;
  select compress(keys), room into :key,:room
  from all_keys
  where seq = &l.;
  quit;

  %let ROOM = %TRIM(&ROOM.);
  %let KEY = %TRIM(&KEY);

  proc sql;
  create table data_&key. as
  select
    put(datepart('CHECK IN DATE'n),EURDFDE7.) as date,
    put(timepart('CHECK IN TIME'n),TIME6.2) as time,
    cat("'",customern) as customerno, /* SQL error here, surplus comma */
  from all_date;
  quit;

  %let l = %eval(&l + 1);
%end;

%mend sample;

%sample;

Note that I already found one place where your code will produce an ERROR.

Run the code with debugging options as already suggested, and then thoroughly study the log. If it doesn't provide a clue, post the log here.

Since the macrio code is data driven, providing a sample dataset against which to test the code will also be very helpful. See my footnotes for this (posting data as code).

[brighterlight]

Thanks for that though it did not solve the problem anyway to use CAPS or not is based on personal preference please at least learn how to respect people on that. 

[Tom]

You don't say where the error is occurring, so we have to guess.

My guess is when you are expanding the macro variables as the argument to the %TRIM() function.

The easiest way to fix that is remove the need for calling the %TRIM() function.

PROC SQL NOPRINT;
SELECT
  COMPRESS(KEYS)
, ROOM
   INTO
  :KEY trimmed
,:ROOM trimmed
FROM ALL_KEYS WHERE SEQ = &l.
;
quit;

You can also simplify your %DO loop by letting SAS increment the counter for you.

%do l = 1 %to &keycount;
...
/* REMOVE THIS %let l = %eval(&l + 1); */
%end;

[brighterlight]

Hi Tom, thanks, this is the error message.

MPRINT(SAMPLE):  PROC SQL NOPRINT;
MPRINT(SAMPLE):  SELECT COMPRESS(KEYS), ROOM INTO:KEY trimmed,:ROOM trimmed FROM ALL_KEYS WHERE SEQ = &l.;
quit;

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
ERROR: More positional parameters found than defined.

[ballardw]

Here on of the most common causes of that specific message:

%macro dummy(message);

   %put Message is &message;
%mend;

%dummy(Some simple text;
%dummy(Text with , embedded);

Note that the macro is defined with a single parameter. If you provide a "parameter", especially a macro variable that may hid the fact that it could contain commas, with commas as part of the value you get that message.

It does not help to show ONLY and error. You have to show the entire code that generates it. Which with macros means the actual macro call.

[brighterlight]

Hi, I posted the code earlier on, its all the way up and only posted error messages when requested. please refer to the top. 

[ballardw]

---

@brighterlight wrote:

Hi, I posted the code earlier on, its all the way up and only posted error messages when requested. please refer to the top. 

---

You posted code from the editor, not the log when you ran the code. this is very obvious because the snippet from the log shows two other lines generated by the MPRINT option. So the entire code from the LOG was not posted.

The reason we ask for the code from the log is that we 1) confirm that the code you show is actually what ran and 2) the position of error messages in relation to the code generated is important.

I suspect in this case that you also needed option SYMBOLGEN to show which value of which macro variable might be an issue.

[Kurt_Bremser]

---

@brighterlight wrote:

Hi Tom, thanks, this is the error message.

MPRINT(SAMPLE):  PROC SQL NOPRINT;
MPRINT(SAMPLE):  SELECT COMPRESS(KEYS), ROOM INTO:KEY trimmed,:ROOM trimmed FROM ALL_KEYS WHERE SEQ = &l.;
quit;

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
ERROR: More positional parameters found than defined.

---

Look at the contents of &room. after the SQL.

[Tom]

Did you remove the %TRIM() calls?

Try adding some macro quoting to the macro variables.

PROC SQL NOPRINT;
SELECT COMPRESS(KEYS), ROOM INTO:KEY,:ROOM FROM ALL_KEYS WHERE SEQ = &l.;
quit;
%let ROOM = %superq(ROOM);
%let KEY = %superq(KEY);

[brighterlight]

thanks Tom, I have tried the above but still to no avail. Could it be data issue ?

[Kurt_Bremser]

When a macro complains about its parameters, you have to inspect those. There's no way around that. Use the debugging options (especially symbolgen) to show what is in the macro variables that are used as macro parameters.

Keep in mind that %trim is not a macro function, but a standard autocall macro.

[brighterlight]

%MACRO SAMPLE;

PROC SQL;
CREATE TABLE ALL_KEYS AS 
SELECT DISTINCT KEYS, ROOM 
FROM ALL_DATA;
QUIT;

PROC SQL;
CREATE TABLE ALL_KEYS AS SELECT * , MONOTONIC() AS SEQ FROM ALL_KEYS;
QUIT;

%let l = 1;
%let keycount = %obscnt(ALL_KEYS);

%do %while(&l le &keycount);

PROC SQL NOPRINT;
SELECT COMPRESS(KEYS), ROOM INTO:KEY,:ROOM FROM ALL_KEYS WHERE SEQ = &l.;
quit;

%let ROOM = %TRIM(&ROOM.);
%let KEY = %TRIM(&KEY);

PROC SQL;
CREATE TABLE DATA_&KEY. AS SELECT
PUT(DATEPART('CHECK IN DATE'n), EURDFDE7.) AS DATE,
PUT(TIMEPART('CHECK IN TIME'n), TIME6.2) AS TIME,
CAT("'",CUSTOMERNO) AS CUSTOMERNO

FROM ALL_DATA
;
QUIT;

%let l = %eval(&l + 1);
%end;
%mend SAMPLE;
%SAMPLE;

ERROR: More positional parameters found than defined.

Hi does anyone know how to fix this error? thanks.

[Quentin]

This looks like a duplicate of https://communities.sas.com/t5/SAS-Enterprise-Guide/ERROR-More-positional-parameters-found-than-defi...

?