Converting space delimited string for use in SAS PROC SQL IN statement with SQL Server

Question

I have a macro function which is defined as follows:

%MACRO Data_Load( Years );
    LIBNAME CCDW_LIB 
            ODBC 
            CONNECTION=SHAREDREAD 
            COMPLETE="DRIVER=SQL Server Native Client 11.0;SERVER=&CCDW_Server_Name;Trusted_Connection=Yes;DATABASE=&CCDW_Data_DB;"
            SCHEMA="&CCDW_Data_Schema"
            PRESERVE_TAB_NAMES=YES 
            PRESERVE_COL_NAMES=YES
            ;
    /* Server and database details obscured for obvious reasons */

    PROC SQL NOPRINT;
        CREATE TABLE WORK.TABLE1 AS 
            SELECT ID
                 , VAL1
            FROM CCDW_LIB.TABLE1
            WHERE YR IN ( &Years )
         ;
    QUIT; RUN;
%MEND;

When I invoke this as %Data_Load( 2018 ) I get an error because YR is actually defined as a VARCHAR and not a NUMERIC. So I tried adding a call to SepList in the WHERE clause (WHERE YR IN ( %SepList( &Years, nest=Q ) )), but this gets an syntax error, even though the MPRINT statement is a correctly formed SQL statement. If I put '2018' in a macro variable prior to the PROC SQL call and then use that variable, the SQL statement runs fine. In fact, I added the following just to see if they were the same and they were.

    %LET Years_IN='2018';
    %LET Years_IN1=%SepList( &Years, nest=Q );
    %Log( "Years_IN = [&Years_IN]");
    %IF &Years_IN1=&Years_IN %THEN %DO;
        %Log("They Match");
    %END;
    %ELSE %DO;
        %Log("The DONT Match");
    %END;

I want to use SepList as the calling program may need more than one year. Any ideas what I am doing wrong? I am running on SAS 9.4 TS Level 1M5 on X64_10PRO if that matters.

Answer 1

Try adding the below custom function, cquote(). It converts a space-delimited list into an individually quoted, comma delimited list. For example, 2012 2013 2014 will be converted into '2012','2013','2014'.

It's a great function to keep in your custom function toolbox. You don't have to use proc fcmp, but it will prevent you from having a huge macro variable full of %sysfunc().

If you get an error that says something about the string being too long, this is a bug in 9.4M5 and a hotfix exists for it. You can safely ignore the error.

proc fcmp outlib=work.funcs.funcs;
    function cquote(str $) $;
        length result $32767;
        result = cats("'",tranwrd(cats(compbl(str))," ", "','"),"'");
        return (result);
    endsub;
run;

options cmplib=work.funcs;

%let years = 2012 2013 2014;
%let yearcq = %sysfunc(cquote(&years.));

Answer 2

I'm assuming you're using Richard DeVenezia's excellent function-style utility macro %seplist: https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist.

Note that when you specify nest=Q it introduces some macro quoting.

Whenever the MPRINT log looks good and you get an error, and there is macro quoting going on, try explicitly unquoting. (SAS should unquote automatically, but it doesn't always).

So try:

WHERE YR IN (%unquote(&Years))

You could also change the last line of the macro definition to be:

%unquote(&emit)

so that it will unquote the value before it is returned.