DATA Step, Macro, Functions and more

PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A CALL EXECUTE INVOKED MACRO

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A CALL EXECUTE INVOKED MACRO

[ Edited ]

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!


Accepted Solutions
Solution
‎05-03-2018 12:29 PM
Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

[ Edited ]

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


All Replies
PROC Star
Posts: 631

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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
Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to SuryaKiran

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

 

Ex of what it is doing,

SELECT (8500 - 8440)

INTO :FCSTV

PROC Star
Posts: 631

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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
Super User
Posts: 6,935

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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?

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to Astounding

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

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to Astounding

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!

Super User
Posts: 6,935

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

[ Edited ]

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.

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to Astounding

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

Super User
Posts: 6,935

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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.

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to Astounding

I did declare it as Global.

Super User
Posts: 6,935

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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.

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

Posted in reply to Astounding

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.

Super User
Posts: 4,030

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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.

Contributor
Posts: 20

Re: PROC SQL SELECT INTO NOT ASSIGNING VALUE TO VARIABLE WHEN INSIDE A MACRO

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 218 views
  • 0 likes
  • 5 in conversation