BookmarkSubscribeRSS Feed
brighterlight
Fluorite | Level 6

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;

 

 

 

14 REPLIES 14
PaigeMiller
Diamond | Level 26

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}

--
Paige Miller
Kurt_Bremser
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

@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
Super User

@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
Super User Tom
Super User

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
Fluorite | Level 6

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

 

Kurt_Bremser
Super User

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
Fluorite | Level 6
%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
Super User

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

?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 4247 views
  • 3 likes
  • 6 in conversation