topic Re: SAS EG very slow in SAS Enterprise Guide

SAS EG very slow

learner_sas — Tue, 30 Jul 2013 15:34:29 GMT

Hello,

I have a dataset with 71k observation and 213 variables. I run following query

data dsn.one;

set dsn.one;

run;

it takes more that 15 mins to run the program . Is it normal . I am wondering if there are certain was to make sure that programs runs quickly. Since I am using local PC engine I made sure that my c drive is almost clean.

Re: SAS EG very slow

ChrisHemedinger — Tue, 30 Jul 2013 15:47:28 GMT

No - that is not normal. It should run much faster.

If you are running this in EG, is it 15 minutes of clock time to run+see results? Or are you getting the 15-minutes number from the SAS log?

Can you supply the complete log (with timing information included)?

Chris

Re: SAS EG very slow

learner_sas — Tue, 30 Jul 2013 16:14:27 GMT

Hello Chris@sas,

it ran quite faster this time. But when I run the program, whole my computer freezes,I have more that 60% of C drive is empty.

1 The SAS System 09:37 Tuesday, July 30, 2013

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='AHRQ_IQI';

4 %LET _CLIENTPROJECTPATH='C:\AHRQQI\IQI\New_IQI_Automate.egp';

5 %LET _CLIENTPROJECTNAME='New_IQI_Automate.egp';

6 %LET _SASPROGRAMFILE='C:\AHRQQI\automate\IQI\AHRQ_IQI.sas';

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGSR TEMP;

12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlB

12 ! lue.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on")

12 ! ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

14 GOPTIONS ACCESSIBLE;

15 data dsn.registry;

16 set dsn.registry;

17 run;

NOTE: There were 71085 observations read from the data set DSN.REGISTRY.

NOTE: The data set DSN.REGISTRY has 71085 observations and 445 variables.

NOTE: DATA statement used (Total process time):

real time 7:25.71

cpu time 2.87 seconds

19 GOPTIONS NOACCESSIBLE;

20 %LET _CLIENTTASKLABEL=;

21 %LET _CLIENTPROJECTPATH=;

22 %LET _CLIENTPROJECTNAME=;

23 %LET _SASPROGRAMFILE=;

25 ;*';*";*/;quit;run;

26 ODS _ALL_ CLOSE;

29 QUIT; RUN;

Re: SAS EG very slow

ChrisHemedinger — Tue, 30 Jul 2013 17:16:31 GMT

Your program, as is, is simply re-writing the data set -- so this might not be a great test. In order to make that work SAS has to make a copy before it replaces the original. But that still doesn't explain the delay completely.

What type of library is DSN? Is it a base SAS library pointing to a local file? Or is it a database?

445 columns is a bit wide, and EG can be a little slow to display wide data in a data grid, but that doesn't explain the time delay here. Are these columns very long (long character lengths)? -- that is, is the data set a very large file? You can run Tasks->Data->Data Set Attributes to find out.

Chris

Re: SAS EG very slow

learner_sas — Tue, 30 Jul 2013 18:03:02 GMT

DSN is local library. I just copied dataset show how slow it is. I tried running same program from regular SAS 9.3 it took me 6 min to run. It is slightly less time then Enterprise Guide.

I have predefined all the variable length since I read .txt file from another location. (312 variable with length 8 and rest with length 1)

Re: SAS EG very slow

ChrisHemedinger — Tue, 30 Jul 2013 18:24:18 GMT

So something like this? Does this take a long time to run?

data test (drop=i);

length n1-n312 $ 8;

length c313-c445 $ 1;

array n{*} n:;

do i=1 to dim(n);

n = cats('val',i);

end;

array c{*} c:;

do i=1 to dim(c);

c = 'C';

end;

do i=1 to 71000;

output;

end;

run;

data testcopy;

set test;

run;

Regardless, you might need to track this with SAS Tech Support so that they can gather more information and provide guidance.

Chris

Re: SAS EG very slow

learner_sas — Tue, 30 Jul 2013 18:33:03 GMT

Almost similar,I do not have to do i = 1 to 71000 since there is no update in values of the variables . Yes but i need to rename (n) some columns/variable. I will do that . Thank you for your help.

Re: SAS EG very slow

ChrisHemedinger — Tue, 30 Jul 2013 18:35:26 GMT

My example code was simply to serve is a benchmark simulation: are all data steps with data of that size slow? Or just your specific table? That's the type of question Tech Support would follow up with.

Chris

Re: SAS EG very slow

Doc_Duke — Tue, 30 Jul 2013 19:16:45 GMT

Learner,

I just ran Chris' sample on my Win 7 - X64 Quad-core with 4 GB memory and got

NOTE: There were 71000 observations read from the data set WORK.TEST.

NOTE: The data set WORK.TESTCOPY has 71000 observations and 445 variables.

NOTE: DATA statement used (Total process time):

real time 0.35 seconds

cpu time 0.35 seconds

If you have a machine with just the minimum amount of memory, you could be doing a lot of swapping of the processes in and out of RAM; that can really slow down the processing.

At any rate, Tech Support can probably uncover the issue pretty quickly.

Doc Muhlbaier

Duke

Re: SAS EG very slow

jakarman — Thu, 01 Aug 2013 06:56:19 GMT

mrustemoglulearner_sas It would be nice to hear from you how you are proceeding with TS

When a solution is found that being shared to the community so we all learn off it.

Re: SAS EG very slow

learner_sas — Thu, 01 Aug 2013 12:50:45 GMT

I will do that.

Re: SAS EG very slow

tlt — Thu, 01 Aug 2013 19:46:29 GMT

Hi there,

Did you notice REAL TIME and CPU TIME in the in the poststed log.

NOTE: There were 71085 observations read from the data set DSN.REGISTRY.

NOTE: The data set DSN.REGISTRY has 71085 observations and 445 variables.

NOTE: DATA statement used (Total process time):

real time 7:25.71

cpu time 2.87 seconds

We could interpret REAL time as the total elapsed time for the data step to run, where as CPU time taken by SAS.

So where goes the 7:25.71-2.87 difference ?

Compared to Doc's log

NOTE: There were 71000 observations read from the data set WORK.TEST.

NOTE: The data set WORK.TESTCOPY has 71000 observations and 445 variables.

NOTE: DATA statement used (Total process time):

real time 0.35 seconds

cpu time 0.35 seconds

From the 2 logs, I would probably say that the difference is due to the DSN library vs the WORK library.

You could run : LIBNAME DSN list; to see how it is allocated.

If it points to a DBMS (ORACLE, TERADATA, MySQL, etc...), the difference is the DBMS.

You can try the new SAS 9.2, OPTIONS DBIDIRECTEXEC, maybe it could help.

If it points to a network file system, the difference is the bandwidth of the FS. You should then contact your network admin.

HTH

PS : When dealing with large datasets through SEGuide, I also cleared the default option that opens the result dataset. Save some time doing so.

Second thought : Could'nt be DBMS behind DSN. Won't be able to modfy DSN.REGISTRY this way Ce message a été modifié par : Thien LE TRI

Re: SAS EG very slow

JeroenJeurissen — Fri, 02 Aug 2013 08:22:15 GMT

Hi Learner_sas,

When I had a similar issue, support gave me the following tip and it worked for me

The problem you mention could occur due to incorrect setting of default metadata server, to configure the same, you can start Integration Technologies configuration wizard from Start menu > Programs > SAS > Utilities > SAS Integeration Technologies Configuration (on the client machine where you use EG from) Click on "Configure Default SAS Metadata Server > Clear Settings > Finish.

Regards Jeroen.