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

[DonnieJ]

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!

[SuryaKiran]

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 

[DonnieJ]

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

Ex of what it is doing,

SELECT (8500 - 8440)

INTO :FCSTV

[SuryaKiran]

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.

[Astounding]

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]

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

[DonnieJ]

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]

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]

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]

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]

I did declare it as Global.

[Astounding]

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]

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]

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]

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