CODEWITHSUNDEEP
sas
CPak
CPak

Reputation: 13581

return TRUE value if column exists in SAS table

I can't quite figure out how to return a TRUE value when a column exists in a table (NOTE - TRUE value can be any value which I can evaluate downstream as TRUE)

Example - Modify code between asterisks

%macro column_exists(data=, target=);
    %local check;

    %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 :check separated by " "
        from    dictionary.columns
        where   libname = "&data_lib" and 
                memname = "&data_data" and
                upcase(name) = upcase("&target");
    quit;

    * RETURN LOGICAL/NUMERIC/CHAR VALUE *
%mend column_exists;

data _null_;
    %let test = %column_exists(data=sashelp.cars, target=mpg_city);
    if &test eq TRUE then %put 'ok'; else %put 'no';
run;

Upvotes: 0

Views: 3954

Answers (1)

Tom
Tom

Reputation: 51581

If you want to create a "function" style macro it cannot generate ANY actual SAS code. It can only contain macro statements.

You can use %sysfunc() macro function to call SAS functions like OPEN() and VARNUM() in macro code.

%macro varexist(ds,var);
%local dsid ;
%let dsid = %sysfunc(open(&ds));
%if (&dsid) %then %sysfunc(varnum(&dsid,&var));
%else 0 ;
%let dsid = %sysfunc(close(&dsid));
%mend varexist;

See this link for the full macro definition with comments and additional features. https://github.com/sasutils/macros/blob/master/varexist.sas

Upvotes: 3

Related Questions