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

I am using a Macro as a means to run code dependent on a condition. My below code works when I am not using a Macro, but now that this code is being ran via a Macro my PROC SQL SELECT INTO is not storing the value inside the variable. Inside the macro I need to create a variable (FCSTV) and it will be passed downstream to be used in another macro. Below is the code....

 

DATA _NULL_;
 CALL SYMPUT('FCST_Q', '1');       /* ENTER FCST QTR     0-NO | 1-YES */

%MACRO FCST_Q;
%GLOBAL FCSTV;
PROC SQL; /* STORE RUNDOWN & TOTAL FCST VARIANCE INTO VARIABLE */
	SELECT (SELECT SALES
	 	FROM RNDN
		WHERE FYQTR = "&FFYQTR"
		AND MTH = '1') - SUM(FCST)
	INTO	:FCSTV
	FROM RDFCST;

%MACRO ADJUST_FCST;
%LOCAL i;
%LOCAL FVAR;
%LET FVAR = "&FCSTV";
	%DO i = 1 %TO &FVAR;
DATA _NULL_; /* ADD REMAINING BALANCE TO FORECAST */
	SET RDFCST;
	IF _N_ = &i THEN DO;
		FCST = FCST + 1;
	END;
%END;
%MEND;
%ADJUST_FCST;

%MEND;

DATA _NULL_; /* TEST IF IN QTR FORECAST MODE */
	IF "&FCST_Q" = '1' THEN CALL EXECUTE('%FCST_Q')

 

 

The variable "FCSTV" is resoling to nothing. It is empty. Again, this code works if not bounded by the Macro %FCST_Q.

 

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
DonnieJ
Obsidian | Level 7

Found this.... Now my code is working

 

Usage Note 23134: Macro variables created with a CALL SYMPUT or SYMPUTX statement or an INTO clause do not resolve when invoked by the CALL EXECUTE routine

 

 

If a CALL EXECUTE routine argument is invoked via a macro invocation or resolves to one, the macro code executes immediately. However, any SAS® statements (such as CALL SYMPUTX and INTO) that are produced by a CALL EXECUTE routine do not execute until after a step boundary has been passed.

Because of this, the %NRSTR function has to be used in the CALL EXECUTE syntax. This enables you to reference the variables created by CALL SYMPUTX or INTO in the code generated by CALL EXECUTE. This workaround delays execution of the macro within the DATA step that contains the CALL EXECUTE.

The use of %NRSTR in the following example masks the call to the %TEST macro when the argument to the CALL EXECUTE routine is evaluated. This causes the %TEST macro invocation to be pushed onto the input stack. Prior to using %NRSTR, all the non-macro statements generated were pushed to the input stack. %NRSTR delays the execution of the %TEST macro until after the RUN statement for the DATA step containing the CALL EXECUTE statement.

Here is an example:

 

 /*sample data set*/
   data a;
     value='Hello';
   run;

   /*sample macro*/
   %macro test(val);
 
   data _null_;
     call symput('num',200);
   run;

   %if &num eq 200 %then %do;
     proc print data=&val;
     run;
   %end;

   %mend;

   /*this will fail*/
   data _null_;
     temp='a';
     call execute('%test('||temp||')');
   run;

   /*work-around*/
   data _null_;
     temp='a';
     call execute('%nrstr(%test('||temp||'))');
   run;

 

Starting with SAS® 9.3 M2, the new DOSUBL function can be used instead of CALL EXECUTE. Here is an example:

Smiley Sad

View solution in original post

17 REPLIES 17
SuryaKiran
Meteorite | Level 14

Your PROC SQL is not defined properly. Why do you want a sub query in select statement. Please provide some sample data you have and what your trying to achieve 

Thanks,
Suryakiran
DonnieJ
Obsidian | Level 7

It is legal to have a subquery in the SELECT statement.

 

Ex of what it is doing,

SELECT (8500 - 8440)

INTO :FCSTV

SuryaKiran
Meteorite | Level 14

I mean you may have issue if your sub-query returns more than one value. 

 

data have;
set sashelp.class(obs=2);
run;
proc sql;
select (select Height from have)-sum(height)
from sashelp.class;
quit;

The above query will throw ERROR: Subquery evaluated to more than one row.If your sub-query always returns only one record than it's fine.

Thanks,
Suryakiran
Astounding
PROC Star

Here are some basic concepts to correct, before we talk about whether you can fix this.

 

DATA steps should end with a RUN statement, and PROC SQL with a QUIT statement.  You are asking for trouble when you use macro language with those statements missing from the program.

 

The interior DATA step does not in any way change the data set RDFCST.  It only reads from RDFCST and doesn't save the changes anywhere.  So why would you expect that you are adding the remaining balance to the forecast?

DonnieJ
Obsidian | Level 7

I am in a mainframe environment. Sorry I did not mention that in the beginning. Thanks. 

DonnieJ
Obsidian | Level 7

I know the interior DATA step wont update. I did that real quick to show the concept.

 

The problem is the PRC SQL is not storing the value in the variable. Once I get this working I can complete my downstream code. Thanks!

Astounding
PROC Star

Running on a mainframe is no excuse for omitting RUN statements.  Without them, the DATA steps don't run when you expect them to, and the value of macro variables created with CALL SYMPUT can differ from what you expect.  Whether that is an issue here is difficult to tell, but it's much easier if you add the RUN statements.

 

You will have to show something closer to the actual code, not the "idea" of the code.  How do you know that you're not just missing a semicolon from the final statement, so that it never runs at all?

 

The code that you have shown never creates &FFYQTR.  If SQL selects 0 records, it may not update the &FCSTV value.

DonnieJ
Obsidian | Level 7

Global variable &FFYQTR is created way upstream in my program. It's resolving.

 

I even tried this as a test...

 

PROC SQL;

SELECT 5

INTO :FCSTV

 

FCSTV still will not resolve.

 

Then I did this as a test...

 

%LET FCSTV = 5

 

and it resolved. It looks like SELECT INTO goes out the window when being used INSIDE A MACRO

Astounding
PROC Star

If that's really the only issue, here's what you are looking at.

 

SELECT INTO :

 

always creates a local macro variable, when it needs to create a new macro variable.  If you need FCSTV to be global, add a %GLOBAL statement before running PROC SQL:

 

%GLOBAL FCSTV;

 

That way, SQL can use the existing global macro variable, instead of having to create a new macro variable.

DonnieJ
Obsidian | Level 7

I did declare it as Global.

Astounding
PROC Star

Sorry, I missed that.

 

So what happens if you insert some diagnostics?

 

Just before the PROC SQL:

 

%let FCSTV = Before;

 

Then just after the SELECT statement:

 

%put &FCSTV;

 

It might help to show the log messages generated by PROC SQL as well.

DonnieJ
Obsidian | Level 7

I assigned a value of 0 using %let. The SELECT INTO did not update the value. It remained as 0. I contacted SAS Tech support because I re-created this problem in a much simpler way and it still does not assign the value inside a Macro. Used the same out not using a macro and it assigned the value.

 

Keep in mind I'm not getting an error....the variable is "resolving", its just resolving as empty/null.

SASKiwi
PROC Star

Comment out the into :macvar bit to test you are actually getting a data row being returned, and a value from the SQL query. No row or no value in the row will result in the macro variable not being updated.

DonnieJ
Obsidian | Level 7

I did, the SQL is valid and does return 1 number (no list). This works not using a macro.

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
  • 17 replies
  • 4464 views
  • 1 like
  • 5 in conversation