I am trying to replace * with % in a string and than build a SQL like statement. Replacing it with tranwrd works perfect and can even use a macro variable to build the like statement.   The only problem I have is that I get a warning in the log : WARNING: Apparent invocation of macro G not resolved.   This is obviously because of the % replacement. I tried several hiding statements but i cant get rit of the warning. Using the macro variable in the SQL statement works also perfect as you can see in the enclosed code.   OPTIONS MPRINT NOMLOGIC NOSYMBOLGEN source source2; %MACRO multi_selection(p_variabele=,p_type=); %GLOBAL &p_variabele; %GLOBAL &p_variabele._zonder; %GLOBAL &p_variabele._sql; %LET v_scheiding=; %IF &p_type=txt %THEN %LET v_scheiding=%STR(%"); %if &&&p_variabele._count=1 %THEN %DO; %LET &p_variabele._zonder=&&&p_variabele.; %LET &p_variabele=&v_scheiding.&&&p_variabele.&v_scheiding.; %LET test=&&&p_variabele._zonder; %IF %SYSFUNC(FIND(&&&p_variabele.,*)) > 0 %THEN %DO; DATA _NULL_; test1="%SYSFUNC(TRANWRD(&&&p_variabele._zonder,*,%))"; test="LIKE '" || test1 || "'"; CALL SYMPUT("&p_variabele._sql",test); RUN; %END; %END; %else %DO; %LET &p_variabele=; %do i=1 %to &&&p_variabele._COUNT; %IF &i=1 %THEN %DO; %LET &p_variabele._zonder=&&&p_variabele.&i; %LET &p_variabele=&v_scheiding.&&&p_variabele.&i.&v_scheiding.; %END; %ELSE %DO; %LET &p_variabele._zonder=&&&p_variabele._zonder.,&&&p_variabele.&i.; %LET &p_variabele=&&&p_variabele.,&v_scheiding.&&&p_variabele.&i.&v_scheiding.; %END; %END; %END; %PUT &&p_variabele._zonder: &&&p_variabele._zonder; %PUT &&p_variabele.: &&&p_variabele.; %PUT &&p_variabele._sql: &&&p_variabele._sql; %MEND; %LET test_sql=; %LET test_count=1; %LET test=*g*; %multi_selection(P_variabele=test,p_type=txt); DATA work.test; txt_field="aabbd"; output; txt_field="agod"; output; txt_field="g987b"; output; txt_field="987bg"; output; RUN; PROC SQL; CREATE TABLE work.test1 AS SELECT * FROM work.test WHERE txt_field &test_sql.; QUIT; ... View more

DATA work.export; DO i=1 to 10000; DO j=1 to 50; a=i; b=j; c=i/j; d=j/i; e=c*d; f=e/c; g="Hi " || a || b; OUTPUT; END; END; RUN; PROC EXPORT DATA=work.export DBMS=excelcs OUTFILE="g:\kcbiar\stp\test.xlsb" REPLACE LABEL; SHEET="Detail"; RUN;   We are using proc export to create a excel file and send it within SAS by e-mail. This works great with xlsx output, but the problem with that format is that the size of the file is very big with a lot of rows an columns (say 300.000 and 20 columns is 81MB even after using ods to zip it is still 52MB). Such a big file we cannot send by e-mail and we don't want to save the data somewhere for the user. We are sending it by e-mail because the query can take up to 2 hours to complete.   To make the excel sheet smaller we could use the xlsb format (sample code included).  That works perfect except when we increase the number of row and columns the excel sheet is empty. In the example i used 500.000 rows and 9 columns. The file size is 9kb and the excel sheet is empty and the name of the sheet is not "Detail" but "A266FF2A662E84b639DA".  I thought maybe the old  limitation of around 65000 rows in excel was the problem, so i reduced the number of rows to 35000. Still the same result.  If I reduce the number of rows to 10000 than it works great or if I reduce the number of columns to 2 than I can use 65000 rows.   It looks like the combination of rows and columns limits the output.  How is this possible and is there something I can do to fix that.   We have SAS 9.4 and offcourse the pcfile server also. ... View more