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
Measuring performance is a delicate matter, it's merely never a linear thing. But yes, your example looks strange.
Lots of things to consider:
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;
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;
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
Ok, I'll try a PROC SUMMARY
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.