topic Re: Is there any solution for this? in SAS Programming

Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 14:01:15 GMT

Hi all

I´m trying to improve my time processing and so I am development a hash object for a faster sort but an error about the amount of memory ocurrs and I not sure if there is any option to avoid it.

Regards

data _null_;

if 0 then set a;

declare hash epi (dataset:"a", ordered:"y");

epi.definekey ("key1","key2");

epi.definedata (all:"yes");

epi.definedone ();

epi.output (dataset: "fastorder");

stop;

run;

ERROR: Hash object added 7864304 items when memory failure occurred.

FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase.

ERROR: The SAS System stopped processing this step because of insufficient memory.

NOTE: DATA statement used (Total process time):

real time 9.06 seconds

user cpu time 7.78 seconds

system cpu time 1.20 seconds

Memory 1137687k

OS Memory 1217248k

Timestamp 15/09/2014 15:48:46

Re: Is there any solution for this?

Jagadishkatam — Mon, 15 Sep 2014 14:14:14 GMT

To the above problem increasing the memsize will help.

to check the current or default memsize, please run the below code

PROC OPTIONS GROUP= MEMORY ;

RUN;

then increase the memsize by options memsize = X;

Thanks,

Jag

Re: Is there any solution for this?

LinusH — Mon, 15 Sep 2014 14:24:59 GMT

You also need sufficient physical RAM available.

It was interrupted at almost 8'' rows, how many more do you need?

Depending on the look-up logic (hit-rate, 1-M etc), hash-tables isn't necessary the most efficient solution.

Re: Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 15:03:01 GMT

Thanks guys.

I increase the amount of memory without any result. I need more physical memory installed in my pc, only 4 gb is very short

Only two images to show you the issue.

Before (1.jpg) and after (2.jpg)

Thanks for your help

P:S : A better way to sort a dataset of 40 millions rows instead of Proc report ?

Regards

Re: Is there any solution for this?

LinusH — Mon, 15 Sep 2014 15:37:23 GMT

This doesn't sound like a job for a client installation?

Are you asking for a better option or not?

If so, you must share of your whole program, the look-up logic.

Re: Is there any solution for this?

gergely_batho — Mon, 15 Sep 2014 16:02:04 GMT

How fast is PROC SORT on your dataset? Have you tried different sorting options?

The default SORTSIZE is sometimes very small, and gives people the feeling, that hash object is significantly better for sorting.

SAS(R) 9.2 Companion for Windows, Second Edition

try:

options sortsize=3G;

proc sort ...

But first increase your MEMSIZE to 3.5G. You can do this in a configuration file or startup option! (NOTwith options memsize=X; statement )

SAS(R) 9.2 Companion for Windows, Second Edition

Re: Is there any solution for this?

SergioSanchez — Mon, 15 Sep 2014 16:09:55 GMT

client installation?

Sorry Linush, I don't know what you mean with it.

One of the task of my job is to make sure that the data load from the source is the same that the data that is working with so we take the data set from the source, order it, take the data that is loaded in the system, order it and verify if the data are the same, wich data are in the source and not in the system and viceversa.

My code is something like this below

data a;

set b (where= vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4);

run;

data x;

set y (where= vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4);

run;

proc sort data = a;

order by key;

proc sort data = x (rename=(var_f = key));

order by key;

data h i j;

merge a (in=w) b(in=q);

if w then output h;

else if g then output i;

else output j;

run;

The problem is that the proc sort takes tooooooooo much time, so I´m trying to replace this piece of the code.

A good alternative was to use a hash object but muy PC hasn't enough memory for perform it with success.

Regards

Re: Is there any solution for this?

jakarman — Mon, 15 Sep 2014 17:30:01 GMT

Sergio replacing a proc sort is a bad idea as that one is one of the most optimal in processing (includes multithreading).

The memory sizing gives me the feeling you are running a 32-bit OS on a 4Gb system.

Only 3Gb is left for your processes and 1Gb has got lost. (yep it is there on your screenshots) And you are having 4 cpu's, could be used all in parallel.

As you are using Windows desktop the SAS installation will give you as much is available for your processing.

Please verify those assumptions. To be corrected when I am wrong. Gergely did already the on sortsize setting.

You are having 40M records en just 8M where able to fit into memory. I assume the recordsize is about 250bytes the dataset size is about 5Gb.

I see 8 numerics for dates (total 64 bytes) there must be some 180 being elsewhere. You have an unique key (key but the input is not ordered that way.

A question:

- Is the order of the key (or other indentifier) in both datasets ordered in the same way?

Than you do not need to do the sorting the merge by is accepting a non sorted input. SAS(R) 9.3 Statements: Reference

I ma not seeing the by statement so it could be a process with some defined ordering values. SAS(R) 9.3 Statements: Reference

When the source is a RDBMS you can retrieve the data ordered.

Your program will become like"

data h i j;

merge

b (in=w where=( vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4) )

y (in=q where=( vardate1<= date1 and vardate2<=date2 and vardate3<=date3 and vardate4<=date4) ) ;

if w then output h;

else if g then output i;

else output j;

run;

There are possible other approaches but the are requiring more knowledge on you data. Questions:

- What is the sizing of your datasets (observations variables recordsize)?

- What I needed for the selection and what data is propagated?

- Which orderings are present and which are important as result?

- Is the merge split as described your wanted process?

An idea. Is one of the datasets just an indicator (the other key merging) in an other order of the same original dataset

just keep that key and the original order present in the dataset and drop all other variables.

Re: Is there any solution for this?

ChrisBrooks — Mon, 15 Sep 2014 21:27:19 GMT

I agree with Jaap, you're most unlikely to be able to improve on the speed of a Proc Sort by using a hash object.

One thing you can do is try using SAS MP Connect to run the two sorts in parallel and then do the merge. I've done similar things myself and it's usually much faster with large data sates, even on a Windows machine.

There's an excellent example of doing just that here SAS/CONNECT(R) 9.2 User's Guide

Chris

Re: Is there any solution for this?

ballardw — Mon, 15 Sep 2014 22:45:45 GMT

I'm also wondering what your hash object actually looks like. That many records makes me think there may be a better way.

Re: Is there any solution for this?

SergioSanchez — Tue, 16 Sep 2014 07:54:39 GMT

Good morning

I left here a proc sort, this proc sort is one of the bigger.

72 proc sort data = dataset_1= dataset_2;

73 by key;

74 run;

NOTA: Se han leído 32498768 observaciones del conj. datos WORK.EC_DIM_PER.

NOTA: El conj. datos WORK.PERSORDENADO tiene 32498768 observaciones y 31 variables.

NOTA: PROCEDIMIENTO SORT utilizado (Tiempo de proceso total):

tiempo real 45:25.58

tiempo de cpu del usuario 42.85 segundos

tiempo de cpu del sistema 2:40.40

Memoria 131842k

Jaap, I atttach you a proc sort screenshort, I hope it can resolved your questions.

Ballardw, Hash hasn`t enough memory for run in my PC. It couldn't load the entire table in memory. Please see my post above.

Thanks all for your help

Re: Is there any solution for this?

jakarman — Tue, 16 Sep 2014 10:35:59 GMT

Sergio,
There is a lot to see with that info. It are not the answers to all of my questions.

It is definitely time for a upgrade SAS 9.1 Windows XP (32) is getting outdated.
The recordlength of 672 with 10 numerics (80byte) and 6 chars 40 (240+ 3 to 9) there must be more chars in that dataset.

In you sort processing 45m45 is the time you did need to wait before it was ready.

You SAS process did use 42s and the system used 2m:40. It is a signal there is a lot time waiting for something.

That "waiting for something" must be I/O (writing/reading from disk) trying to focus on minimizing that.

Your file size must by about 22Gb with these numbers. reading 22Gb and writing that back is 44Gb data-transfer, not counted the intermediate utility files.

As the hardware is fixed some tuning options. SAS(R) 9.4 Language Reference: Concepts, Third Edition

- compress=binary You are having many char variables it can gain for reading and writing the visible datasets

- Bufsize=64k bufno=8 SAS(R) 9.4 Companion for Windows, Third Edition as attempt to minimize IO.

The Utilities files are getting options in 9.4 these are out of scope with this.

Chris proposal to spilt the dataset in eg 10 parts (2Gb) sorting them each and merging can help as avoiding a lot of those intermediate big files.

The sort memory usage is saying just 131842k being used. That one is the SAS(R) 9.4 Companion for Windows, Third Edition sortsize was set with much smaller values in older releases (64Kb default 9.1). Please verify this one.

There are possible other approaches but the are requiring more knowledge on you data. Questions:

- (answered)

- What is needed for the selection and what data is propagated?

- Which orderings are present and which are important as result?

- Is the merge split as described your wanted process?

Re: Is there any solution for this?

SergioSanchez — Tue, 16 Sep 2014 13:07:03 GMT

Thanks Jaap for this "free of charge lessons" :smileylaugh:.

I`d try to increase the amount of memory in the buffer and total too.

As for your questions, I coul say

- The most of the datases I need the hole set, in case I only need subset it´s already implemented in the code

- Is some dataset there is no order at all, in other the order is not the one I need

- In fact it is not what I was thinking, but it works is doesn´t matter.

What about multiprocessing in local, I'd try to explain myselft. Is posible to run the two short in parallel?. I read about this but I know sure if it could be posible running a local sesion and "emulate" a server on local machine.

Thanks

Re: Is there any solution for this?

jakarman — Tue, 16 Sep 2014 13:33:16 GMT

Sergio, I have to thank you keeping my brains sharp for hearing and trying to understand the issue and trying to find acceptable ways out.

The "proc sort" will use multi threading (using all 4 cores) as it reaches some point. It will split up your dataset in smaller pieces sort them and doe a merger when writing out the results. There is some point with data sizing where the efficiency of this process rapidly falls by resource consumption on the system. As you are already short on that I would not advice to run those in parallel.
If the sorting of let us take a 2 Gb is acceptable fast (<1min) then sorting 10 of them in serial will take a factor 10 times this time.

Merging that 10 files together will be the last step. Do not expect miracles from this. Copying 2Gb is still a lot and will need the time for that.

Running processes parallel can be done by batch scripting or using MP-connect part of SAS/connect.

Re: Is there any solution for this?

Ksharp — Wed, 17 Sep 2014 12:21:49 GMT

I don't think using Hash Table to sort would be faster than using proc sort . If the table is too big . Here is a proposal .

data F M;

set sashelp.class;

if sex='F' then output F;

else if sex='M' then output M;

run;

proc append base=want data=F force;run;

proc append base=want data=M force;run;

Or simply make an index for it ,and you don't sort it again every time you need to BY statement .

Xia Keshan

Message was edited by: xia keshan

Re: Is there any solution for this?

AmySwinford — Wed, 17 Sep 2014 12:31:50 GMT

HI Sergio,

As stated, hash is not the answer here. I don't think MP will work either unless you have CONNECT on your installation, you can check that by running proc setinit. If you don't have CONNECT, you still may be able to marginally increase the speed of your sort by defragmenting your disk and/or breaking up the data into smaller chunks and sorting those. Your sort needs about 3 times the size of the data to execute, and if you have a fragmented disk then it has to get the space from multiple spots. If you split the data up into 40 different datasets of 1 million observations each, sort each of them by your keys, then set them together using the by variables, you will have a single sorted data set, and it may run faster than your sort of a single big dataset.

Re: Is there any solution for this?

SergioSanchez — Wed, 17 Sep 2014 13:48:00 GMT

Thanks all for your comments

I have made one text splitting the dataset in 10 smaller datasets, the results given are below

options fullstimer;

proc sort data = Dataset_A out = Dataset_B (compress=binary);

by KEY;

run;

NOTE: There were 32498768 observations read from the data set Dataset_A.

NOTE: The data set WORK.PERSONSHORT has 32498768 observations and 31 variables.

NOTE: Compressing data set Dataset_B decreased size by 71.08 percent.

Compressed is 391645 pages; un-compressed would require 1354116 pages.

NOTE: PROCEDURE SORT used (Total process time):

real time 44:04.28

user cpu time 1:54.30

system cpu time 1:13.66

Memory 66929k

OS Memory 100216k

Timestamp 16/09/2014 17:22:45

Now I splitt the dataset with a macro (thanks to Selvaratnam Sridharma for the tool )

data _null_;

33 if 0 then set ec_dim_per nobs=count;

34 call symput('numobs',put(count,8.));

35 run;

36 %let n=%sysevalf(&numobs/&num,ceil);

37 data %do J=1 %to &num ; DATASET_B_&J %end; ;

38 set DATASET_B;

39 %do I=1 %to &num;

40 if %eval(&n*(&i-1)) <_n_ <= %eval(&n*&I)

41 then output DATASET_B_&I;

42 %end;

43 run;

44 %mend split;

45 %split (10)

NOTE: DATA STEP stopped due to looping.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.00 seconds

NOTE: There were 32498768 observations read from the data set WORK.DATASET_B.

NOTE: The data set WORK.DATASET_B_1 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_2 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_3 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_4 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_5 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_6 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_7 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_8 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_9 has 3249877 observations and 31 variables.

NOTE: The data set WORK.DATASET_B_10 has 3249875 observations and 31 variables.

NOTE: DATA statement used (Total process time):

real time 15:13.22

cpu time 49.68 seconds

NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of

this session.

Another macro (this time made by me ) to run all the proc sort throught the datasets

46 %macro sort;

47 %do i=1 %to 10;

48 proc sort data = DATASET_B_&i;

49 by KEY;

50 run;

51 %end;

52 run;

53 %mend sort;

54 %short

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_1.

NOTE: The data set WORK.DATASET_B_1 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:19.91

cpu time 17.59 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_2.

NOTE: The data set WORK.DATASET_B_2 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 5:08.46

cpu time 19.56 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_3.

NOTE: The data set WORK.DATASET_B_3 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 2:29.01

cpu time 19.51 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_4.

NOTE: The data set WORK.DATASET_B_4 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:03.43

cpu time 17.03 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_5.

NOTE: The data set WORK.DATASET_B_5 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 6:03.90

cpu time 18.95 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_6.

NOTE: The data set WORK.DATASET_B_6 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 6:44.73

cpu time 19.23 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_7.

NOTE: The data set WORK.DATASET_B_7 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 2:48.26

cpu time 18.72 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_8.

NOTE: The data set WORK.DATASET_B_8 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 3:36.37

cpu time 20.17 seconds

NOTE: There were 3249877 observations read from the data set WORK.DATASET_B_9.

NOTE: The data set WORK.DATASET_B_9 has 3249877 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 4:41.83

cpu time 19.00 seconds

NOTE: There were 3249875 observations read from the data set WORK.DATASET_B_10.

NOTE: The data set WORK.DATASET_B_10 has 3249875 observations and 31 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 3:17.87

cpu time 19.48 seconds

the sum of the all sorts procedures are almost 41 min without counting the 15 min for the macro to work. And we still need a merge to build the dataset again (by the way, what do you think is the best way to merge all the datasets?)

As you can see split in 10 dataset isn't faster than the original proc sort. If I able to free space I'd try with perhaps 20 small datasets.

AmySwinford, I have SAS/ACCESS ready for use in my PC but I never use it and to be honest I don´t know where to start to make it work in local mode (server/cliente in the same machine).

I think defragmenting the hard disk is a very good idea , I´d try if my user have admin privileges,...

Regards

Re: Is there any solution for this?

KachiM — Wed, 17 Sep 2014 13:50:47 GMT

Probably knowledge of maximum and minimum values for KEY1 and KEY2 might be useful for array solution.

Re: Is there any solution for this?

RW9 — Wed, 17 Sep 2014 14:51:36 GMT

Not much different to what you already have been provided with, but split each type of "KEY" out into a dataset, then set back together again (note depending on number of keys, this could create a lot of small datasets):

/* Some test data */

data have;

attrib key format=$5.;

array var{15} $20.;

do i=1 to 1000; /*32498768;*/

key=strip(put(mod(i,234),best.));

output;

end;

run;

/* Get list of unique KEY values */

proc sql;

create table WORK.LOOP as

select distinct

KEY

from WORK.HAVE;

quit;

/* Generate code to split data into each key value */

data _null_;

set loop;

call execute('data work.srt'||strip(key)||'; set work.have (where=(key="'||strip(key)||'")); run;');

run;

/* Generate final datastep to append back together again */

data _null_;

set loop end=last;

if _n_=1 then call execute('data want; set ');

call execute(' work.srt'||strip(key));

if last then call execute(';run;');

run;

Re: Is there any solution for this?

KachiM — Wed, 17 Sep 2014 17:01:59 GMT

If your Key1 and Key2 are numbers and that their minimum and maximum values are small enough(your system memory can hold) to use a temporary array, you may reduce the workload much.

But you need to find out the maximum occurrence of Key1 or Key2 whichever is maximum. The array will hold the Record ID(RID) of the observation for the Key1 and Key2.

You then use this RID in POINT = option of SET and get the sorted output.

Here is an example using SASHELP.CLASS with AGE and HEIGHT. Though Height has a decimal point, it does not matter to use it as array-index, the decimal is

ignored.

The same idea can be used with Hash Table too.

data class;

set sashelp.class;

run;

data need;

array k[11:16, 51:72, 2] _temporary_;

if _n_ = 1 then do;

do _n_ = 1 by 1 until(eof);

set class(keep = age height) end = eof;

i = 1;

do while(k[age, height, i] > 0); i + 1; end;

k[age, height, i] = _n_;

end;

do i = lbound1(k) to hbound1(k);

do j = lbound2(k) to hbound2(k);

m = 1;

do while(k[i, j, m] > 0 );

ptr = k[i, j, m];

set class point = ptr;

output;

m + 1;

if m > hbound3(k) then leave;

end;

stop;

drop i j m;

run;

proc print data = need;

run;

Note the use of

array k[11:16, 51:72, 2] _temporary_;

By the homework, 11:16 for Age, 51:72 for Height and the maximum occurrence of 2 are found. The homework is needed to minimize the array sizes. The RID is _N_;