Help using Base SAS procedures

slow on Proc Sql - summation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

slow on Proc Sql - summation

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


Accepted Solutions
Solution
‎04-05-2018 11:26 AM
Occasional Contributor
Posts: 8

Re: slow on Proc Sql - summation

Posted in reply to eric23263
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

View solution in original post


All Replies
Super User
Posts: 5,890

Re: slow on Proc Sql - summation

Posted in reply to eric23263

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
Data never sleeps
Occasional Contributor
Posts: 8

Re: slow on Proc Sql - summation

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;

 

Super User
Posts: 10,283

Re: slow on Proc Sql - summation

Posted in reply to eric23263

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 3,067

Re: slow on Proc Sql - summation

[ Edited ]
Posted in reply to eric23263

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

--
Paige Miller
Occasional Contributor
Posts: 8

Re: slow on Proc Sql - summation

Posted in reply to PaigeMiller

Ok, I'll try a PROC SUMMARY

Solution
‎04-05-2018 11:26 AM
Occasional Contributor
Posts: 8

Re: slow on Proc Sql - summation

Posted in reply to eric23263
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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 189 views
  • 0 likes
  • 4 in conversation