代码之家 › 专栏 › 技术社区 › CPak

在proc sql宏中动态排除列名

sas

0

CPak · 技术社区 · 7 年前

我在一个宏函数中有一个proc sql语句,从中选择列名 dictionary.columns

%symdel keepnames;

%macro test(data=, col=);
    %global keepnames;

    %let data_lib = %sysfunc(upcase(%sysfunc(scan("&data", 1, "."))));
    %let data_data = %sysfunc(upcase(%sysfunc(scan("&data", 2, "."))));
    %put &data_lib;
    %put &data_data;

    proc sql noprint;
        select name into :keepnames separated by " "
        from    dictionary.columns
        where   libname = "&data_lib" and
        memname = "&data_data" and
        upcase(name) not like upcase("&col.");
    quit;
%mend test;

%test(data=sashelp.cars, col=mpg w)
%put &keepnames;

理想情况下 col 争论会变成 %mpg%, %w% 因此,不包括名称中带有mpg或w的任何列名。

我遇到了几个问题。首先,我不太明白如何隐藏 % 从宏处理器。我试着用 %str() 在几个方面,但没有运气。第二,我不能轻易地添加单词周围的符号 上校 论点感谢您的帮助!

2 回复 | 直到 7 年前

1

2

Richard 7 年前

例如,将宏参数名称更改为更好的名称

%macro fetch_names (data=, dropPattern=, resultVar=fetchedNames)
   ...
%mend;

考虑传递一个正则表达式,而不是一个必须重复迭代的空间分隔的值列表。

 %let fetchedNames = ;
 %fetch_names (
   data = sashelp.cars
 , dropPattern = mpg|w     /* <------- regular expression pattern */
 , resultVar = fetchedNames
 )

宏的内部结构也类似。

改变
into :keepnames 到
into :&resultVar
upcase(name) not like upcase("&col.") 到
not prxmatch("/&dropPattern./i", name)

2

0

Amir 7 年前

我在下面使用了contains操作符,并通过col中的参数循环,为每个排除生成单独的测试。由于某些原因,我没有车,所以我使用了class:

%symdel keepnames;

%macro test(data=, col=);
    %global keepnames;

    %let data_lib = %sysfunc(upcase(%sysfunc(scan("&data", 1, "."))));
    %let data_data = %sysfunc(upcase(%sysfunc(scan("&data", 2, "."))));
    %put &data_lib;
    %put &data_data;
    %let i = 1;
    %let exclusion = %scan(&col,&i);

    proc sql noprint;
        select name into :keepnames separated by " "
        from    dictionary.columns
        where   libname = "&data_lib" and
        memname = "&data_data"

        %do %while(&exclusion ne );
            and upcase(name) not contains upcase("&exclusion")

            %let i = %eval(&i + 1);
            %let exclusion = %scan(&col,&i);
        %end;
        ;
    quit;
%mend test;

option mprint;
%test(data=sashelp.class, col=ame x)
%put &keepnames;

%test(data=sashelp.class)
%put &keepnames;

%test(data=sashelp.class, col=e)
%put &keepnames;