Suppress display macro variable contents in the SAS log.

[cbsmith_aus]

I want to suppress the display of the contents of a particular macro variable in the SAS log without turning off MPRINT.  I still want the code written to the log but not the contents of a macro variable that could hold up to 5,000 separated values (account numbers).  Is there a macro function that suppresses the display of a macro variable in the SAS log, like you might have for passwords?

MPRINT(QUERY_LAST_PAYMENT_DATE): options nomgen nosgen mprint;
MPRINT(QUERY_LAST_PAYMENT_DATE): proc sql;
MPRINT(QUERY_LAST_PAYMENT_DATE): insert into work.output_table

select a.account_number, bal_prin_amt, interest_due,
extract_date,interest_paid_amt_ltd

from ORACLE.TABLE1 (orhints='/*+parallel(16)*/' readbuff=1000000
where=("01DEC15:00:00:00"dt between valid_From and valid_to)) as a

inner join ORACLE.TABLE2 (orhints='/*+parallel(16)*/' readbuff=1000000 where=(extract_date="01DEC15:00:00:00"dt)) as b

on a.account_number=b.account_number
and extract_date between valid_from and valid_to

where a.account_number in ('1111111','22222','333333','44444') ;
NOTE: 1 row was inserted into WORK.OUTPUT_TABLE.

[ballardw]

Placing 5,000 values into a single macro variable often indicates a poor choice of structure or code. That much data generally belongs in a data set. If the values were in a data set then likely this wouldn't be a issue.

Is the issue making the log to too long to read or sensitivity of data? If sensitivity that is another indication that the values may belong in a data set.

I'm not aware of any way to turn off display of single macro variables in this situation.

[cbsmith_aus]

The current solution uses a SAS dataset filtering very large Oracle tables.  As a result SAS has to extract all rows in the Oracle tables before filtering with the SAS lookup table.  This iteration is repeated around 300 times.  My initial proposed solution was to pass the entire SAS lookup table into an Oracle table but a lack of write access to Oracle killed that solution.  So I am back to my crude SAS macro which gets around this by passing the macro as code to the Oracle DBMS. 

I am more concerned with the length of the SAS log than the sensitivity of the information.  Ideally I would still want to see the resolution of other macro variables for the query in the SAS log but if that is not possible then I will use Options statements before and after the query to toggle macro resolution written to the SAS log.  Thanks for your reply.

[Tom]

You could put the statement you want to run that references the macro variable into another macro variable and use some macro quoting to prevent the macro variable some being resolved. Then you could use %PUT statement to echo that to the log and use %UNQUOTE() to execute it.

Example:

1934   %macro test;
1935   %local code ;
1936   %let code=select * from sashelp.class where name in (%nrstr(&)mvar);
1937
1938   proc sql;
1939   options mprint;
1940   %unquote(&code) ;
1941   options nomprint;
1942   %put Running statement: &code ;
1943   %unquote(&code);
1944   option mprint;
1945   quit;
1946
1947   %mend test;
1948
1949   %test;
MPRINT(TEST):   proc sql;
MPRINT(TEST):   options mprint;
MPRINT(TEST):   select * from sashelp.class where name in ('Alfred','Alice') ;
MPRINT(TEST):   options nomprint;
Running statement: select * from sashelp.class where name in (&mvar)
MPRINT(TEST):   quit;

[SASKiwi]

So you are blocked from even using Oracle temporary tables? In my experience that is very unusual as temporary table use is common even in exclusively Oracle processing often to hold intermediate query results. Loading your SAS lookup table into a temporary Oracle table would be the ideal solution.

I'd be kicking up a fuss with the Oracle DBA and / or management if I struck such a roadblock. BTW I'm a heavy user of SQL Server temporary tables as they are a huge aid to efficient querying.

[ballardw]

---

@cbsmith_aus wrote:

The current solution uses a SAS dataset filtering very large Oracle tables.  As a result SAS has to extract all rows in the Oracle tables before filtering with the SAS lookup table.  This iteration is repeated around 300 times.  My initial proposed solution was to pass the entire SAS lookup table into an Oracle table but a lack of write access to Oracle killed that solution.  So I am back to my crude SAS macro which gets around this by passing the macro as code to the Oracle DBMS. 

I am more concerned with the length of the SAS log than the sensitivity of the information.  Ideally I would still want to see the resolution of other macro variables for the query in the SAS log but if that is not possible then I will use Options statements before and after the query to toggle macro resolution written to the SAS log.  Thanks for your reply.

---

The highlighted text above is further example that macro is likely a poor choice.

Really time to talk to your admins, both SAS and the Oracle DB perhaps. The approach chosen seems to be a likely to impact other use of the db as well since parsing extremely long repeated Where clauses could have lots of performance issues.

Or why is it necessary to to have MPRINT generating all of that output in the first place.