topic Reg:Sorting Data by Hash Keys in SAS Procedures

Reg:Sorting Data by Hash Keys

My_SAS — Wed, 28 Sep 2011 06:47:43 GMT

HI i having a dataset with 72 variables and having 12lacs observation
i want to sort the data on two variables but do reduce the time i want to do it by hash hey
how can i do it.

proc sort data=temp;
by name date;
run;

Reg:Sorting Data by Hash Keys

FriedEgg — Wed, 28 Sep 2011 16:02:55 GMT

Are you confusing a hash join with a type of sort? Are you wanting to create a new key as a hash value of the concatenation of the name and date fields? Or something else? A sort of a file with only 72 variables and 1,200,000 observations should be very quick already... What kind of system are you using? You could try using the tagsort option.

Reg:Sorting Data by Hash Keys

Tom — Wed, 28 Sep 2011 16:12:47 GMT

Are you just talking about the TAGSORT option on PROC SORT?

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146878.htm

Reg:Sorting Data by Hash Keys

FriedEgg — Wed, 28 Sep 2011 18:54:04 GMT

There is a balance in the performance offered by tagsorting. If you use the tagsort option you remove the ability for the sort to be multithreaded. So if your performance is hindered by I/O avaiablility for your utility files from the sort, then tagsort is a good option. If you are not bounded by I/O or disk space for these files then you will likely see hindered sort performance. You could try allocating additional memory to the sort procedure (see the sortsize option and other options in the memory/performace groups). On my system, with default memory options a table with 4,000 columns and 1,000,000 obs took about 5 minutes with a lot of system competition. However if I run the same sort with the tagsort option on it took over 10 minutes.

Reg:Sorting Data by Hash Keys

Ksharp — Thu, 29 Sep 2011 08:35:57 GMT

You only need to use argument ordered: 'a' in hash table constructor.

and put these name into definekey () .Hash will automatically sort them for you.

declare hash ha1(dataset:'temp',ordered: 'a');

ha1.definekey('name','age');

Ksharp

Reg:Sorting Data by Hash Keys

My_SAS — Thu, 29 Sep 2011 12:58:51 GMT

data temp;

input name age;

declare hash ha1(dataset:'temp',ordered: 'a');

ha1.definekey('name','age');

cards;

23 45

67 89

12 56

run;

proc print;

run;

I have tryed this but it did not worked how can i do it and how can i use the hash key in proc sort:

proc sort data=temp;

by ha1.definekey;

run;

if i have used this iam getting error. can any one help me

Reg:Sorting Data by Hash Keys

Tom — Thu, 29 Sep 2011 13:55:15 GMT

I am really not clear on what you mean by "use the hash key" in proc sort.

Why do you want to sort your data?

Would an index serve your needs?

Also how large is your dataset? What number does 12lacs represent?

Reg:Sorting Data by Hash Keys

art297 — Thu, 29 Sep 2011 14:00:18 GMT

Tom, I can't answer why the OP would want to do this, but I can answer what the number represents: 1.2 million. See: http://en.wikipedia.org/wiki/Lakh

Reg:Sorting Data by Hash Keys

FriedEgg — Thu, 29 Sep 2011 14:56:11 GMT

Good thinking Ksharp, I do believe this is the method the OP was trying to ask about utilizing. However, I do not think he understands it's use or implementation.

Re: Reg:Sorting Data by Hash Keys

Ksharp — Fri, 30 Sep 2011 06:01:05 GMT

You do not use Hash Table correctly.

See the followig code of example.

BTW. I am not sure this method will be more efficient than proc sort;

data temp;
input name age;
cards;
23 45
67 89
12 56
;
run;
data _null_;
*load dataset temp into Hash Table;
if 0 then set temp;
declare hash ha1(dataset:'temp',ordered: 'a');
  ha1.definekey('name','age');
  ha1.definedata('name','age');
  ha1.definedone();
*output Hash Table to a dataset;
  ha1.output(dataset:'want');
run;
*now see the data has already been sort by name age;
proc print data=want;run;

Ksharp