topic Re: SAS memory issue in SAS Procedures

SAS memory issue

lokendra_devangan_corecompete_com — Thu, 02 Aug 2018 17:53:39 GMT

Dear all,

I am merging two datasets of size 30GB and 2 GB using proc sql. How much memory is required to accomplish this merge.

thanks

Lokendra

Re: SAS memory issue

kiranv_ — Thu, 02 Aug 2018 19:00:11 GMT

There are many aspects which impact a query. but it looks this is perfect case where it is better to use hash join instead of SQL join. Please furnish more information related to data, so that someone can help you better. Usually databases have the capacity to guess amount of space and time a query runs through a explain plan, if not sure SAS has something available that can give space kind of information.

Re: SAS memory issue

SASKiwi — Fri, 03 Aug 2018 00:43:28 GMT

If you run your query using the SAS option FULLSTIMER it will report how much memory it is using. You can increase your MEMSIZE setting if you are running out of memory - please note this option must be set at the invocation of your SAS session.

Re: SAS memory issue

Kurt_Bremser — Fri, 03 Aug 2018 05:41:15 GMT

That's not so much a memory problem, but a disk storage problem. Proc sql will need space to do the sorting and for the utility file, and the size of your output will depend on the variables named in the select, and the relationship between the two tables.

Depending on the data and your needs, you might find that SQL is not a good solution at all. See Maxim 10.

Re: SAS memory issue

ChrisNZ — Mon, 06 Aug 2018 04:16:09 GMT

This question cannot be answered without you giving a lot more information.

At least:

1. Are the tables sorted? indexed?

2. What sort of join is it? Cartesian, inner, left etc?

3. How many rows in each table?

4. Is(are) the join key(s) unique in either or both tables?

5. What is (roughly) the expected number of rows in the output table?