topic Re: slow on Proc Sql - summation in SAS Procedures

slow on Proc Sql - summation

eric23263 — Thu, 05 Apr 2018 13:38:00 GMT

Why is it too long? 26 minutes with summation, but only 0.03 secondes without summation !!!

I have 82 columns... How to reduce this slowness?

proc sql;
create table pour_TDB.TDB35_A as SELECT   Codefinal , GS_final ,  AnSuiv,
sum(eff_app_total17) as eff_app_total17, sum(eff_app_bul_16) as eff_app_bul_16, sum(eff_app_bul_15) as eff_app_bul_15, sum(eff_app_bul_14) as eff_app_bul_14,

etc.........
 
FROM pour_TDB.TDB35
GROUP BY Codefinal , GS_final, AnSuiv
order by Codefinal;

NOTE: Table POUR_TDB.TDB35_A created, with 2272 rows and 82 columns.
1198 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 26:14.24
cpu time 26:11.52
run;quit;

*******************************************;

proc sql;
create table pour_TDB.TDB35_B as SELECT  Codefinal ,GS_final , AnSuiv,
 eff_app_total17 as eff_app_total17, eff_app_bul_16 as eff_app_bul_16, eff_app_bul_15 as eff_app_bul_15, eff_app_bul_14 as eff_app_bul_14,

etc ..................

FROM pour_TDB.TDB35
order by Codefinal;
run;quit;

NOTE: Table POUR_TDB.TDB35_B created, with 2604 rows and 82 columns.
1243 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 secondes
cpu time 0.03 secondes

NB : I'm not english spoken

Re: slow on Proc Sql - summation

LinusH — Thu, 05 Apr 2018 13:47:07 GMT

Measuring performance is a delicate matter, it's merely never a linear thing. But yes, your example looks strange.

Lots of things to consider:

Is data local or remote? Even external RDBMS...
If remote, any other users can be competing on resources
If those queries are run in the same session, or quite close in time, consider any cashing effects
OPTIONS FULLSTIMER can give more detailed information, and other PROC SQL options like _method and _tree.
Monitoring from OS perspective can give more hints
The full log might give some more hints
etc

Re: slow on Proc Sql - summation

PaigeMiller — Thu, 05 Apr 2018 13:58:44 GMT

While this doesn't really answer the original question ...

If the data extract with no summations takes 0.03 seconds, why not do the extract that way and then do the summation in PROC SUMMARY? This way you are not competing for resources and you won't run into the other problems mentioned by @LinusH. And it's guaranteed to be faster.

#ProcSummaryRulez

Re: slow on Proc Sql - summation

eric23263 — Thu, 05 Apr 2018 14:36:45 GMT

Yes, prog and datas are on local disk, there is no other user (SAS 9.4 TS Level 1M1 X64_7PRO)

Here the log :

NOTE: Table POUR_TDB.TDB35_A created, with 2272 rows and 82 columns.
1301 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           26:00.35
      user cpu time       9:29.59
      system cpu time     16:29.60
      memory              1573743.75k
      OS Memory           1597836.00k
      Timestamp           05/04/2018 04:30:24 PM
      Step Count                        52 Switch Count 0

1302 options nofullstimer;

Re: slow on Proc Sql - summation

eric23263 — Thu, 05 Apr 2018 14:40:43 GMT

Ok, I'll try a PROC SUMMARY

Re: slow on Proc Sql - summation

Kurt_Bremser — Thu, 05 Apr 2018 14:43:51 GMT

Try a sort followed by proc summary:

proc sort data=pour_TDB.TDB35;
by Codefinal GS_final AnSuiv;
run;

proc summary data=pour_TDB.TDB35;
by Codefinal GS_final AnSuiv;
var ......;
output out=pour_TDB.TDB35_a sum=;
run;

Re: slow on Proc Sql - summation

eric23263 — Thu, 05 Apr 2018 15:25:44 GMT

Thank yoiu for all
NOTE: There were 2604 observations read from the data set POUR_TDB.TDB35.
NOTE: The data set POUR_TDB.TDB35_A has 2272 observations and 82 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.04 secondes
cpu time 0.04 secondes