create table table&i as
select x , idx, z, sum(y) as tot&i from database
where idx between &i and %eval(&i+1)
group by x;
This loop creates 50 little tables referring to the variable in the where clause. After that I merge all the tables. The time process is very long, probably ralated to the huge database (3.000.000 records, 10GB). How can i avoid the 50 tables?
Your idx column aren't used as group by column nor as a input to a statistical function. This means that your data aren't summarized at all...?
Can't still see why you are using this logic, why you can't use an ordinary sum()/group by? What is your final delivery?
First of all, sorry but i have 2 problem: SAS and english language.
I don't use idx as group because:
idx 1 =period 1
idx 2 =period 2
Data are summarized selecting period 1 and 2, period 3 and 4.....
With 100 idx values i should have 50 different sums...
If you run your program, you should probably see something like this in the log:
NOTE: The query requires remerging summary statistics back with the original data.
Which means that your result will have detail data in your output data.
Still knowing exactly what you want, but I assume you want to sum two periods together in each output record.
Try to have some mechanism that maps two periods together. Since I don't know what kind of periods we're talking about, I can't give a complete solution. One way if you could create a SAS format that maps periods pairwise to a single value, and apply it in the SELECT clause. Then you could do like this:
/* Create format before this step */
create table table&i as
select x , put(idx,MyPeriodFmt.) as id, z, sum(y) as tot_y
group by x, calculated id, z;
If our periods are dates or months you can probably us some SAS functions to group periods direclty in the SQL.
Conclussion, no need for looping and merging data afterwards, whic will probably save a lot of processing time.
> Hi Linus,
> i split and then merge the data because I don't know
> if with the proc SQL i can calculate n sum with
> different where clause.
although you cannot apply different where clauses, you can achieve the effect with the CASE-WHEN-ELSE structure
This example takes the macro loop inside the select statement to create 30 new columns tot1 to tot30.
create table table_all_30 as
, idx, z
%do i= 1 to 30 ;
, case when idx GE &i and idx LE %eval(&i+1)
then sum(y) else 0 end as tot&i
group by x, idx, z
naming the new columns tot1 to tot30 ( not idx1 to idx30)