05-02-2018 01:04 PM - edited 05-03-2018 12:56 PM
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!
05-03-2018 12:19 PM - edited 05-03-2018 12:29 PM
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:
05-02-2018 01:16 PM
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
05-02-2018 01:28 PM
It is legal to have a subquery in the SELECT statement.
Ex of what it is doing,
SELECT (8500 - 8440)
05-02-2018 01:41 PM
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.
05-02-2018 01:36 PM
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?
05-02-2018 01:41 PM
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!
05-02-2018 01:50 PM - edited 05-02-2018 01:55 PM
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.
05-02-2018 02:34 PM
Global variable &FFYQTR is created way upstream in my program. It's resolving.
I even tried this as a test...
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
05-02-2018 02:38 PM
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:
That way, SQL can use the existing global macro variable, instead of having to create a new macro variable.
05-02-2018 03:04 PM
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:
It might help to show the log messages generated by PROC SQL as well.
05-02-2018 07:09 PM
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.
05-02-2018 07:22 PM
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.
Need further help from the community? Please ask a new question.