topic Re: apply queries to more than one table in SAS Procedures

apply queries to more than one table

Bloker — Wed, 07 Jan 2009 16:05:07 GMT

Hi

I have 11 tables that i want to run a query to extract certain data (columns) from.
all tables have same column's names

how can i create a query to extract the columns i need from all tables?

each table has like 100 columns so its hideous to do it 11 times! there should be a smarter way to do it...

(I am a newbie btw)

Re: apply queries to more than one table

1162 — Wed, 07 Jan 2009 18:03:10 GMT

A macro would be a good way to handle this. Put your query into the macro and then just run it 11 times, each time with a new dataset name. In the example below, each loop of the macro creates a tmpfile which is then appended to a master outfile. The tmpfile is then deleted to prepare for the next macro invocation.

Example:
[pre]
%macro runquery (infile=, outfile=);

proc sql;
create tmpfile as
select col1, col3, col5
from &infile;
quit;

proc append base=&outfile data=tmpfile;
run;

proc datasets library=work nolist;
delete tmpfile;
run;
quit;

%mend runquery;

%runquery (infile=work.table1, outfile=work.endtable);
%runquery (infile=work.table2, outfile=work.endtable);
.
.
.
%runquery (infile=work.table11, outfile=work.endtable);
[/pre]

Re: apply queries to more than one table

ssas — Tue, 13 Jan 2009 11:13:28 GMT

i feel you can also do in this way

%let s1=table1; * input table name1;
%let s2=outputtale1; * output table name;
%let s3=ap1; * all your append files dataset;

* if your exporting all your files from the external source you can use &s1, &s2 in proc import;
data libname.&s2; * if you want to save all your specific tables in a library;
set &s1;
keep col1 col2 col3 ......;
run;
* you can stop here if you want seperate tables;

proc appennd base=&s2 data=&s3;
run;
or
data tablename;
set &s2 &s3.....;
run;