topic Re: SPDE-code optimisation in SAS Programming

SPDE-code optimisation

Kayalvizhi — Thu, 24 Mar 2022 16:20:57 GMT

SPDE PROC SQL code utilising a large SAS work space. Please suggests me ideas to optimize and use the work space to minimal.

-In current scenario, I'm performing PROC SQL join on 36 datasets having 12 columns each.

Kindly consider below limitations:

-Without redirecting - work or - utiloc locations

-Without using system commands

Please let me know for further details.

Re: SPDE-code optimisation

Sajid01 — Thu, 24 Mar 2022 17:50:56 GMT

@Kayalvizhi
Proc SQL joins do have large workspace requirement. Why don't you try using data step. That works row by row.

Re: SPDE-code optimisation

SASKiwi — Fri, 25 Mar 2022 00:16:54 GMT

Please post your code so we can see the type of joins you are using. We can't offer much useful advice until we see what you are currently doing.

Re: SPDE-code optimisation

Kayalvizhi — Fri, 25 Mar 2022 03:46:46 GMT

Proc sql;
Create table spde.new from select
*
<some functions are used>
from spde.temp %do I=1 %to 36;
Left join. Spde.month&i ; %end;
Run

Re: SPDE-code optimisation

Kayalvizhi — Fri, 25 Mar 2022 03:59:09 GMT

I’m migrating huge set of codes from SAS to SPDE( using HDFS). So I doesn’t want to make much changes to existing code.

Can you please tell me why SPDE SQL using SAS workspace, and why not HDFS workspace.

Is there a way to use HDFS as workspace when using SPDE

Re: SPDE-code optimisation

SASKiwi — Fri, 25 Mar 2022 04:12:01 GMT

Without knowing more about the data and the joining, that would work better with appending using the SET statement:

data want;
  set spde.month1 - spde.month36;
run;

Re: SPDE-code optimisation

Kurt_Bremser — Fri, 25 Mar 2022 08:46:13 GMT

@Kayalvizhi wrote:
Proc sql;
Create table spde.new from select
*
<some functions are used>
from spde.temp %do I=1 %to 36;
Left join. Spde.month&i ; %end;
Run

This code produces only syntax errors.

there's a dot where it must not be
the semicolon at the end of the first spde.month&i terminates the CREATE TABLE, the others will be invalid
there are no ON conditions
PROC SQL needs to be terminated with a QUIT, and no RUNs are needed, as SQL statements are always executed immediately.

Please post something that at least resembles your real code, and give us at least some ideas of the datasets involved (variables used for matching, variables contained, match relationships).

Re: SPDE-code optimisation

LinusH — Mon, 28 Mar 2022 12:43:39 GMT

You can use the SPDEUTILLOC option to specify where SPDE utility files will be stored.

Unclear how this wirks with hdfs implmentaions of SPDE data.

Maybe the environment variable SPDE_HADOOP_WORK_PATH is the way to go: variable https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/engspdehdfsug/n07ieiyvjz2wrxn10n7bs7xupvkc.htm

You can also use SPDESORTSIZE if you have available memory, which will impove performance and lessen the burdon on utility file areas.

Re: SPDE-code optimisation

Kayalvizhi — Tue, 29 Mar 2022 10:50:16 GMT

Hi,

Thanks!!.

>SPDEUTILLOC- Not privileged to use options while invocation/change config file. So unable to use this option.

>SPDESORTSIZE- Increasing spdesortsize uses more/full workspace (which I don't want to happen).

>SPDE_HADOOP_WORK_PATH - I wanted to know more on how it works, because when I give a HDFS location, it still seems to use workspace and not HDFS path.

Re: SPDE-code optimisation

LinusH — Thu, 31 Mar 2022 15:52:56 GMT

I think the idea is to use as much RAM as possible by tweaking SPDESORTSIZE, hence use less disk space.

I haven't used SPDE in hdfs other than in a test environment (and that was quite a while ago), and I just refer to the documentation.

But since at least I think the documentation does not clarify how different SPDE options work in a hdfs scenario, I suggest that you contact SAS tech support to get advice/clarifications.