topic Re: Optimization in SAS Programming

Optimization

David_Billa — Wed, 09 Oct 2019 07:14:11 GMT

Is there a chance to combine the below steps into 2 or 3?

%let table_name=test;

data want (keep=flt_id col_nm operant value );
set oper_flt;
where TBL_NM="&table_name";
run;

proc sql noprint;
select catx(' ',col_nm,operant,value)
  into :where separated by ' or '
  from want
;
quit;

proc sql;
create table temp as select * from &table_name
 where &where ;
run;

data oper_flt (keep=flt_id);
set temp;
run;

Re: Optimization

gamotte — Wed, 09 Oct 2019 09:07:50 GMT

Hello,

I don't really understand what the last datastep is for so i ignored it.

%let table_name=sashelp.class;

data oper_flt;
    infile cards dlm=',' dsd;
    length table_name $32 col_nm $32 operant $2;
    input flt_id table_name col_nm operant value;
    cards;
1,sashelp.class,age,=,12
1,sashelp.class,weight,ge,90
;
run;

data _NULL_;
    call execute('proc sql noprint; CREATE TABLE want AS SELECT * FROM &table_name. WHERE ');

    do until(fend);
        set oper_flt (where=(table_name="&table_name.")) end=fend;
        call execute(catx(' ', col_nm, operant, value, 'OR'));
    end;
    call execute('0; quit;');
    stop;
run;

Re: Optimization

Astounding — Wed, 09 Oct 2019 12:42:27 GMT

This is untested, but looks like it should work:

%let table_name=test;

proc sql noprint;
select catx(' ',col_nm,operant,value)
  into :where separated by ' or '
  from oper_flt
  where TBL_NM="&table_name";
  ;
create table oper_flt2 as select flt_id from &table_name
  where &where ;
quit;

When I say "should work" it means you have to test it since you have the data. This code should produce the same result as your original code, except that the final data set name is OPER_FLT2 instead of replacing OPER_FLT.