topic SQL Loop in SAS Procedures

SQL Loop

HDSimo — Mon, 07 Mar 2011 09:54:45 GMT

Hi,
I'm trying to optimize a SQL loop like this

%do i=1 %to 100 %by 2;

proc sql;
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;
quit;
%end;

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?

Thanks in advance

Simone

Re: SQL Loop

DBailey — Mon, 07 Mar 2011 13:26:36 GMT

How is the table DATABASE sorted?

Re: SQL Loop

LinusH — Mon, 07 Mar 2011 13:35:23 GMT

How do you merge the tables at the end?
Pls describe the "business problem". Knowing more about what you are trying to accomplish will help finding alternatives.

/Linus

Re: SQL Loop

HDSimo — Mon, 07 Mar 2011 13:49:46 GMT

Hi,
database is not sorted. The code that i have posted is a simpler version of the original (it contains subquery). I'm trying to calculate different "sums" (idx could be a time period).

Thanks

Re: SQL Loop

LinusH — Mon, 07 Mar 2011 13:53:28 GMT

This still does not explain why you need to split and merge the data.

/Linus

Re: SQL Loop

HDSimo — Mon, 07 Mar 2011 13:59:57 GMT

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.

Re: SQL Loop

LinusH — Mon, 07 Mar 2011 14:07:37 GMT

Hmmm...
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?

/Linus

Re: SQL Loop

HDSimo — Mon, 07 Mar 2011 14:18:46 GMT

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...

Re: SQL Loop

LinusH — Mon, 07 Mar 2011 15:39:25 GMT

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 */

proc sql;
create table table&i as
select x , put(idx,MyPeriodFmt.) as id, z, sum(y) as tot_y
from database
group by x, calculated id, z;
quit;

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.

/Linus

Re: SQL Loop

DBailey — Mon, 07 Mar 2011 16:26:12 GMT

I think the other commenters are correct..but I believe you would experience significant performance gains if you sorted the table correctly or created appropriate indices.

Re: SQL Loop

Peter_C — Mon, 07 Mar 2011 17:29:04 GMT

> 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.
proc sql;
create table table_all_30 as
select x
, 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
%end
from database
group by x, idx, z
;
quit; naming the new columns tot1 to tot30 ( not idx1 to idx30)

Message was edited by: Peter.C

Re: SQL Loop

SPR — Mon, 07 Mar 2011 19:12:38 GMT

Hello HDSimo,

Try to use views instead on your 50 tables. In this case you create a real table only when you merge all views.

Sincerely,
SPR

Re: SQL Loop

Ksharp — Tue, 08 Mar 2011 02:31:25 GMT

Hi.Maybe you write code like this to avoid 50 dataset.

[pre]
proc sql;
create table result as

select x , idx, z, sum(y) as tot&i from database
where idx between 1 and 2
group by x;

%do i=2 %to 100 %by 2;

union all corresponding

select x , idx, z, sum(y) as tot&i from database
where idx between &i and %eval(&i+1)
group by x;

%end;

quit;

Ksharp

Re: SQL Loop

HDSimo — Tue, 08 Mar 2011 08:01:40 GMT

Thanks!
A lot of good advices!

Thank you again