topic Re: Proc Sql and Proc Sort execution failure in SAS Programming

Proc Sql and Proc Sort execution failure

vapodaca11 — Thu, 04 Oct 2018 08:38:32 GMT

I am working with large datasets - 5TB a piece.

When I run a sort procedure I save them to the actual space which is over 100TB, but for some reason they are running through the tmp folders which only has 1TB so the sort execution fails. How do I get the dataset to sort in the folder space and not the tmp space to keep if from failing.

So by folder space I mean it matches up with a Libame, so mc.dataset

Re: Proc Sql and Proc Sort execution failure

Kurt_Bremser — Thu, 04 Oct 2018 08:52:36 GMT

Temporary utility files are created in the WORK location, or in the location specified by the UTILLOC option. You need to change these options in the configuration if you want to have your WORK and/or UTILLOC somewhere else than /tmp.

Which configuration you need to change depends on the way you use SAS (Base SAS with Display Manager, Enterprise Guide with Local server, remote workspace server with EG or SAS Studio, SAS in batch mode).

Re: Proc Sql and Proc Sort execution failure

Kurt_Bremser — Thu, 04 Oct 2018 11:18:56 GMT

One thing you can try is to sort the dataset in a proc sort step, and use the tagsort option. This means that only the by variables and an observation pointer go into the utility file, but you pay for that in terms of performance.

Tagsort is also good when you have used the compress=yes option on a dataset and managed to get a high compression rate.

Re: Proc Sql and Proc Sort execution failure

Ksharp — Thu, 04 Oct 2018 11:23:48 GMT

This table is too big for PROC SORT or SQL .

I would suggest other method to sort data:

data F M;

set sashelp.class;

select(sex);

when('F') output F;

when('M') output M;

otherwise;

run;

data want;

set F M;

run;