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.
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.
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.
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;
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.
@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.
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
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.
Ready to level-up your skills? Choose your own adventure.