:INTO Variable ERROR?

[BU2B]

Good morning all,

I'm not convinced this is my issue but I'm going to start here.  I'm creating a list of variables with PROC SQL:

PROC SQL NOPRINT ;
    SELECT ESIID FORMAT = $22. INTO :METER SEPARATED BY ' ' 
		FROM MINING_ESIIDS;
QUIT;

There's 117 observations all of which are the same length except one, the 110th OBS, it's LENGTH 22.  I have a macro that calls each :METER variable and does a MERGE and some calculations.  However, as soon as it hits the :METER with the odd length I get the following error: 

ERROR: Overflow has occurred; evaluation is terminated.

ERROR: The condition in the %DO %UNTIL loop, &_METER=, yielded an invalid or missing value, .

The macro will stop executing.

ERROR: The macro ESIID will stop executing.

Here's the macro code:

%MACRO ESIID;
	%GLOBAL _METER;
    %LET I = 1;
	%LET _METER = %SCAN(&METER, &I);
	%DO %UNTIL (&_METER=);

DATA _NULL_;
	METER_STR = "&_METER";
	CALL SYMPUTX('METER_STR',"'"||METER_STR||"'");
	PUT METER_STR;
RUN;

	DATA ESIIDR_&_METER (KEEP = DATE1 INTERVAL ESIID2 MWH MN_COGS);
		SET INTERVAL_DATA_2;
		WHERE ESIID2 = &METER_STR;
		MERGE AUXCTS;
			IF ZONE = 'Houston' THEN MN_COGS = MWH * LZ_HOUSTON;
				IF ZONE = 'North' THEN MN_COGS = MWH * LZ_NORTH;
				 	IF ZONE = 'South' THEN MN_COGS = MWH * LZ_SOUTH;
					 	IF ZONE = 'West' THEN MN_COGS = MWH * LZ_WEST;
RUN;

%LET I = %EVAL(&I+1);
	%LET _METER = %SCAN(&METER, &I);
	%END;
	%MEND; 
%ESIID;

When I exclude the odd meter from the table the macro finishes with no errors.  I've read where I may need a Hotfix for this, but I'm reluctant to install it without trying to debug it.  Any help would be appreciated!

[Community_Guide]

Hello @BU2B,

Your question requires more details before experts can help. Can you revise your question to include more information? 

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
  
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message.  From there you can adjust the title and add more details to the body of the message.  Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @ballardw

.

[Astounding]

You'll have to explain what your final DATA step is supposed to accomplish.  It's most unusual for a MERGE statement to name just a single data set, and rare for the same DATA step to contain both a SET and a MERGE statement.

[BU2B]

The dataset INTERVAL_DATA_2 has approximately 2,000 15 minute values for each of the 117 meters.  The MERGE dataset AUXCTS has a price for each 15 minute interval.  The macro is creating a dataset for each :METER from INTERVAL_DATA_2 and calculating a COGS with the price from AUXCTS.  Hope this helps.  If you need more info let me know.

Steve

[RW9]

What is it your actually trying to do.  Start by providing some test data in the form of a datastep and what you want out at the end.  I can say quite simply that macro is not the place to be doing data processing, it is just a find/replace system, nothing more.  Creating lists of data will lead you into many difficulties.  Its very hard to help as you never provide what meter macro variable is, but at a guess I say you are trying to group out your data into several datasets based on some sort of list.  This really is not a good idea, takes more space on disk, more resources to process, and means all your code is going to be far harder to write.  What normally you would do in SAS is to apply categorisations within the dataset, i.e. a variable which can be a set via a specific set of criteria, then this categorisation variable is used in by group processing.  For instance, a very basic example, I could take all the various results in gender from sashelp.class, then write macro code to loop over each of these, or I could simply do:

proc print data=sashelp.class;
  by gender;
  title "Group: #byval1";
run;

So much simpler coding, no need to split out etc.

Also, please avoid coding all in shouting case, it makes code far harder to read.

[ballardw]

Could you show us what the value actually is that causes the problem? Since you are not specifying a delimiter for %SCAN then any of

ASCII:

blank ! $ % & ( ) * + , - . / ; < ^¦ 

or ~ if the ^ isn't available

EBCDIC:
blank ! $ % & ( ) * + , - . / ; < ¬ | ¢¦ 

are default delimiters. If the VALUE of your variable has any of these then the SCAN likely returned a value you don't expect. I would suspect the most likely candidate would be the dash, - , character.

I will say that a number of hard to trace errors comes from actually placing quotes into macro variables.

You could replace

DATA _NULL_;
	METER_STR = "&_METER";
	CALL SYMPUTX('METER_STR',"'"||METER_STR||"'");
	PUT METER_STR;
RUN;

with

%let meter_str=%quote(&_meter.);

Or skip placing quotes in the macro variable at all an use

WHERE ESIID2 = "&_METER"

Have you run the macro with the options MPRINT and SYMBOLGEN to see what is generated?

[BU2B]

Here's a snipet from the values in the variable :METER.  As soon as the macro hits the odd length record it crashes.

[Quentin · Accepted solution]

Really big numbers can cause overflows like that.  I could replicate your error with:

%MACRO ESIID;
  %GLOBAL _METER;
  %LET I = 1;
  %LET _METER = %SCAN(&METER, &I);
  %DO %UNTIL (&_METER=);
    %put &=_METER ;
    %LET I = %EVAL(&I+1);
    %LET _METER = %SCAN(&METER, &I);
  %END ;
%MEND ;

%let meter=1 2 1234567890123456789012 ;
%ESIID ;

Your %DO UNTIL sees that really big number and chokes.  One hack to avoid this problem is to add a character to the comparison, so that %DO UNTIL will see a text string, rather than a number.  Since all your checking for is a null value, I would think this would this work.

Below I add a Z to the string:

%DO %UNTIL (Z&_METER=Z);

Generally it's a good idea to declare macro variables created inside a macro as local macro variables, and to pass variables to macros as parameters rather than rely on global macro variables. But those are separate issues.

[BU2B]

Thanks so much Quentin!  I've spent hours trying to figure this out...

[Tom]

So that is one of the problems with using %EVAL(&mvar=) as a test for an empty macro variable.  Personally I like to use %LENGTH() to test for empty macro variables.

But why not just eliminate the test completely by using an iterative %DO loop instead?

%local i _meter ;
%do i=1 %to %sysfunc(countw(&meter));
  %let _meter=%scan(&meter,&i);
...

[BU2B]

Thanks for the tip Tom.  I will give that a try and get back to you.  I'm always looking for more elegant ways to write code.  Mine's kind of sloppy.. 

[RW9]

proc sort data=meter out=loop nodupkey;
  by meter;
run;
data _null_;
  set loop;
  call execute(cats('data meter_',meter,'; set meter; where meter="',meter,'"; run;'));
run;

No loops, no macro code, no restrictions on number of elements etc.  Although as before, splitting data up into smaller datasets is not a good idea from storage or coding point of view.