How to split table if some values contain special characters

Question

While working on a SAS macro course I encountered a problem I cannot solve. The exercise is to create a macro, that would split the table into seperate tables based on the distinct values of a column. The issue is, that some values contain special characters (I'm looking at you Mercedez-benz!), so it doesn't generate a table for that value. Solution they used used data step, and didn't generate any tables when encountered this problem, so there was no help there.

%macro SplitTableSQL (tab, col);
proc sql noprint;
    select distinct &col
        into :tabList1-
        from &tab
    ;
    select distinct 
            cat('where &col = "', &col, '"') 
        into :whenList1-
        from &tab
    ;
quit;
%do i=1 %to &sqlobs;
    proc sql;
        create table &&tabList&i as
        select *
            from &tab
            &&whenList&i
        ;
    quit;
%end;
                


%mend SplitTableSQL;

options mprint mlogic;
%SplitTableSQL(sashelp.cars, make);
options nomprint nomlogic;

Answer 1

Obviously, there a a dozen ways to solve this. You could use the Compress Function and remove special characters and do something like this

%macro SplitTableSQL (tab, col);
proc sql noprint;
    select distinct compress(&col, , 'kad')
        into :tabList1-
        from &tab
    ;
    select distinct 
            cat('where &col = "', &col, '"') 
        into :whenList1-
        from &tab
    ;
quit;
%do i=1 %to &sqlobs;
    proc sql;
        create table &&tabList&i as
        select *
            from &tab
            &&whenList&i
        ;
    quit;
%end;
                


%mend SplitTableSQL;

options mprint mlogic;
%SplitTableSQL(sashelp.cars, make);
options nomprint nomlogic;