BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eric23263
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
eric23263
Fluorite | Level 6
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

6 REPLIES 6
LinusH
Tourmaline | Level 20

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
eric23263
Fluorite | Level 6

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;

 

Kurt_Bremser
Super User

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;
PaigeMiller
Diamond | Level 26

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
eric23263
Fluorite | Level 6

Ok, I'll try a PROC SUMMARY

eric23263
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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