topic Re: quoting issues in SAS Programming

quoting issues

acordes — Wed, 14 Aug 2019 17:01:27 GMT

It works but it feels complicated with the ''' workaround.

How Do I use the quoting functions wisely in this case?

Instead of changing the firstobs= and obs= and filename manually, I use the construct proc sql + cats + select into to execute the dynamic code.

With the last proc sql it works but I'd like to make it smarter and I have never really understood the %quote and %bquote and alike.

I feel that they could come at my rescue here.

Thanks, Arne

filename output "L:\B2C_PORTFOLIO2SCORE_results_ext_aug19_7.csv" encoding="utf-8";

PROC EXPORT data=crm_scored_hq(firstobs=1200001 obs=1400000) 
outfile=output dbms=csv replace;
delimiter=';';
run;


data test;

do fi=1 to 1379961 by 200000;
la=fi-1;
output;
end;

run;

data test2;
merge test(keep=fi) test(keep=la firstobs=2) end=eof;
if eof then la=fi+200000;
OBS=_N_;
run;

PROC SQL; select catt("filename output " || 
'''L:\B2C_PORTFOLIO2SCORE_results_ext_aug19_' ||
STRIP(PUT(OBS,3.)) ||".csv' encoding="||'''utf-8'''||";
PROC EXPORT data=crm_scored_hq(firstobs="|| STRIP(PUT(FI,8.)) || "OBS=" ||STRIP(PUT(LA,8.)) ||") 
outfile=output dbms=csv replace;
delimiter=';';
run;")
into :List separated by " "
from WORK.test2
;
quit;
 
&List.;* execute statements in mvar List;

Re: quoting issues

PaigeMiller — Wed, 14 Aug 2019 17:17:40 GMT

I'm not seeing how %QUOTE or %BQUOTE would help here.

Perhaps the non-macro QUOTE function might be helpful here, but it doesn't look like it would save a lot of programming.

Re: quoting issues

Tom — Wed, 14 Aug 2019 19:20:58 GMT

You haven't really explained what your code is trying to do or even what problem you are having with it.

But in general I find it much easier to use a data step to generate code into a file. You can then examine the file and check its contents until you get the code generation logic right. (Or just generate it to the LOG) Once you know it works just %INCLUDE the file. In addition you get to take advantage of all of the nice features of the DATA step and the PUT statement and FORMATs to make the code generation easier.

filename code temp;

data _null_;
  set test2;
  file code;
  length filename $200 ;
  filename = cats('L:\B2C_PORTFOLIO2SCORE_results_ext_aug19_',obs,'.csv');
  put 'filename output ' filename :$quote. ' encoding="utf-8" ;'
    /  'PROC EXPORT data=crm_scored_hq(firstobs=' FI 'OBS=' LA ')'
       ' outfile=output dbms=csv replace'
    / ';'
    /  '  delimiter=";";'
    /  'run;'
  ;
run;

%include code / source2;

Note you can use the FILENAME= option on the FILE statement to generate all of the delimited files in one pass of a single data step without all of the other rigmarole.

Re: quoting issues

Reeza — Wed, 14 Aug 2019 19:01:14 GMT

Can you use CALL EXECUTE and a data step instead? That would generate no issues that I can see and would avoid the PROC SQL step as well as macro variables or macros or having to include the code either.

Re: quoting issues

Kurt_Bremser — Wed, 14 Aug 2019 20:05:36 GMT

If you need to create dynamic code from a dataset, use call execute() in a data step.

Re: quoting issues

PaigeMiller — Wed, 14 Aug 2019 20:12:28 GMT

@Reeza wrote:
Can you use CALL EXECUTE and a data step instead? That would generate no issues that I can see and would avoid the PROC SQL step as well as macro variables or macros or having to include the code either.

But @acordes said his code works, so I don't see the point of re-coding this using CALL EXECUTE. Maybe the next time he needs to do something similar, then he can use CALL EXECUTE.

Re: quoting issues

acordes — Mon, 09 Sep 2019 09:16:59 GMT

What does

filename :$quote.

do?

Re: quoting issues

acordes — Mon, 09 Sep 2019 09:18:31 GMT

Thank you, I use call execute quite often and I feel comfortable with it as long it doesn't rely on quoting stuff...

Re: quoting issues

Tom — Mon, 09 Sep 2019 11:13:59 GMT

@acordes wrote:

What does
filename :$quote.
do?

It is PART of the PUT statement. FILENAME is the name of variable. $QUOTE. is the format to use when putting the variable. The colon modifier says to continue to use the list mode of the put statement instead of formatted mode, even though there is a format specification.