From a Dataset
%macro tq84_sqlDataSetSel1Col_M;
%let stmt = %sysfunc(dequote(&stmt ));
%local sep;
%let sep = %nrstr(' ');
proc sql noprint;
select *
into :tq84_sqlDataSetSel1Col_VAR separated by &sep
from ( &stmt )
;
quit;
%mend tq84_sqlDataSetSel1Col_M;
proc fcmp outlib=tq84_lib.funcs.tq84;
function tq84_sqlDataSetSel1Col_F(stmt$) $ 32767;
length tq84_sqlDataSetSel1Col_VAR $32767;
rc = run_macro('tq84_sqlDataSetSel1Col_M', stmt, tq84_sqlDataSetSel1Col_VAR);
if rc = 0 then return (tq84_sqlDataSetSel1Col_VAR);
return(cat(' rc = ', rc));
endsub;
run;
options cmplib=tq84_lib.funcs;
%macro tq84_sqlDataSetSel1Col(stmt);
%sysfunc(tq84_sqlDataSetSel1Col_F(&stmt))
%mend tq84_sqlDataSetSel1Col;
Testing:
The following example creates a dataset and the uses
%tq84_sqlDataSetSel1Col to select the variable names from
dictionary.columns and then, it selects the values of
col_one from the created data set.
data tq84_dir_sel_test;
col_one = 'abc'; col_two = 42; col_three = '28aug17'd; output;
col_one = 'def'; col_two = 99; col_three = '13sep13'd; output;
col_one = 'ghi'; col_two = -1; col_three = '15oct15'd; output;
run;
%let col_names = %tq84_sqlDataSetSel1Col(
select
name
from
dictionary.columns
where
libname = 'WORK' and
memname = 'TQ84_DIR_SEL_TEST'
);
%put col_names = &col_names; /* col_names = col_one col_two col_three */
%let col_one_values = %tq84_sqlDataSetSel1Col(
select
col_one
from
tq84_dir_sel_test
);
%put col_one_values = &col_one_values; /* ol_one_values = abc def ghi */
%let col_one_array = %tq84_array(&col_one_values);
%tq84_arrayApply(&col_one_array, %nrstr(
%put value = &this;
))
/*
value = abc
value = def
value = ghi
*/
From a DBMS
%macro tq84_sqlDBMSSel1Col_M;
%let dbmsLib=%sysfunc(dequote(&dbmsLib));
%let stmt =%sysfunc(dequote(&stmt ));
%local sep;
%let sep = %nrstr(' ');
proc sql noprint;
connect using &dbmsLib;
select *
into :tq84_sqlDBMSSel1Col_VAR
separated by &sep
from connection to &dbmsLib (
&stmt
);
quit;
%mend tq84_sqlDBMSSel1Col_M;
proc fcmp outlib=tq84_lib.funcs.tq84;
function tq84_sqlDBMSSel1Col_F(dbmsLib$, stmt$) $ 32767;
length tq84_sqlDBMSSel1Col_VAR $32767;
rc = run_macro('tq84_sqlDBMSSel1Col_M', dbmsLib, stmt, tq84_sqlDBMSSel1Col_VAR);
if rc = 0 then return (tq84_sqlDBMSSel1Col_VAR);
return(cat(' rc = ', rc));
endsub;
run;
options cmplib=tq84_lib.funcs;
%macro tq84_sqlDBMSSel1Col(dbmsLib, stmt);
%sysfunc(tq84_sqlDBMSSel1Col_F(&dbmsLib, &stmt))
%mend tq84_sqlDBMSSel1Col;
The following test uses
%tq84_sqlDBMSSel1Col_F to select Oracle table names from all_tables. With
%tq84_array, the result is converted into a macro array.
%let ora_user = rene;
%let ora_password = secretGarden;
%let ora_server = ora12.renenyffenegger.ch;
libname tq84_ora
oracle
user = &ora_user
password = &ora_password
path = &ora_server
sql_functions = all
db_length_semantics_byte = no;
%let tables = %tq84_sqlDBMSSel1Col(tq84_ora, %nrstr(select table_name from user_tables));
/*%put tables=&tables;*/
%let tablesArray = %tq84_array(&tables);
%tq84_arrayApply(&tablesArray, %nrstr(
%put table = &this;
))
%put sysdate on Oracle is
%tq84_sqlDBMSSel1Col(tq84_ora,
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual
);