@ScottBass wrote:
@jpprovost wrote:
Hi folks,
I have more than 50 tables with identical structure (they all have the same number of columns and same datatype). I would like to do the following code which returns 1 line of data, but for all my 50+ tables. Then, I would also like to regroup all those lines into one dataset.
PROC SQL;
CREATE TABLE DASHBOARD2 AS
( SELECT AVG(VALUE) AS MOYENNE, MAX(VALUE) AS MAXIMUM, MIN(VALUE) AS MINIMUM, SUM(VALUE) AS SOMME, COUNT(*) AS NBOBS
FROM TEST01
);
QUIT;
Is there a way to do so for all the 50+ tables and to get one dataset with 50+ lines with thoses informations (avg, max, min, sum, nbos)?
@novinosrin is correct...
Your proc sql is going to return one line, with avg, max, min, sum, and count for all the rows in test01.
Get proc summary to create the same results for your single test01 dataset. Read the doc on proc summary if you need to.
Now that it's working for one dataset, just append all your datasets into one, using the indsname option to capture the contributing dataset name. You could even use a data step view if your datasets are really large. Read the doc on the indsname option if you need to.
Then, just use the dataset name as a class variable in proc summary. This is analogous to group by in SQL.
If you insist on proc sql, then append the datasets as above, and use the dataset name in a group by in proc sql:
data test01 (where=(x between 0 and 10))
test02 (where=(x between 10 and 20))
test03 (where=(x between 20 and 30))
;
do x=0 to 30;
output;
end;
run;
data append / view=append;
* or test: if you don't have any extra files named test... ;
set test01-test03 indsname=n;
name=n;
run;
proc sql;
create table foo as
select
name
,avg(x) as avg
,min(x) as min
,max(x) as max
from
append
group by
name
;
quit;
proc summary data=append nway noprint;
class name;
var x;
output out=foo2 (drop=_type_ _freq_)
mean=avg
min=min
max=max
;
run;
Macros are great - I use them every day. But it's just as important to know when NOT to use a macro.
Thank to you @ScottBass.
Of course macros are great, I try to use is as much as I can.
However, do you have any tips/hints of when macros should NOT be used?
Regards,
... View more
