topic Re: load dataset from client to CAS in SAS Programming

load dataset from client to CAS

dipand — Tue, 13 Sep 2022 08:16:49 GMT

Hello,
i have a dataset with 16.000.000 record and 136 column. I load this dataset every day on cas. My procedure is this:

data hseq.HSEQ_INSP_FIND_TEMP1;
set staging.HSEQ_INSP_FIND_TEMP
;
run;

proc casutil incaslib="hseq" outcaslib="hseq";
droptable casdata="HSEQ_INSP_FIND_TEMP" quiet;
promote casdata="HSEQ_INSP_FIND_TEMP1" casout="HSEQ_INSP_FIND_TEMP";
save casdata="HSEQ_INSP_FIND_TEMP" casout="HSEQ_INSP_FIND_TEMP" replace;
quit;

but is very slowly about over two hours.
is there another way for load dataset in cas?

Thank you,

Re: load dataset from client to CAS

smm662002 — Thu, 06 Oct 2022 19:21:58 GMT

Hi,
I don't know if you still need help, but a diffrent way to load data to CAS is to use SAS DI Studio jobs scheduled out of working hours (if possible).

Alternativeley you can use a proc fedsql to load the data but source and destination have to be from caslibs. After the upload you can use proc casutil or proc cas to promote the table.
Regards,
Smm662002

Re: load dataset from client to CAS

dipand — Fri, 07 Oct 2022 06:36:29 GMT

unfortunately my dataset is on sas 9.4 and i must load they on the cas, because i read this data from sap/bw. the sas job start all day in different hours after finish the job sap/bw and the time for load this dataset to very large and i can't major performance

Re: load dataset from client to CAS

LinusH — Mon, 10 Oct 2022 06:30:15 GMT

This post have some suggestions how to trigger a parallel load from SAS datasets:

https://blogs.sas.com/content/sgf/2019/10/17/how-to-parallel-load-and-compress-a-sas-cloud-analytic-services-cas-table/

If your process is critical, and you have an MPP CAS, you can always explore these options:

https://communities.sas.com/t5/SAS-Communities-Library/4-Approaches-for-Parallel-Data-Loading-to-CAS/ta-p/370955

Re: load dataset from client to CAS

Patrick — Mon, 10 Oct 2022 09:58:56 GMT

Just as a comment to the first link @LinusH shared to an article from 2019: I made the experience of negative query performance impact when compressing a CAS table (using compress=true).

If you're on a very recent Viya version then use memoryFormat="DVR" instead. https://communities.sas.com/t5/SAS-Communities-Library/Viya-2020-1-CAS-Duplicate-Value-Reduction/ta-p/707049

Re: load dataset from client to CAS

DerylHollick — Mon, 07 Nov 2022 00:21:38 GMT

I have seen this method load faster. You could try it instead of the DATA step.

%let path=%sysfunc(pathname(staging));
proc cas;
   table.upload / path="&path/HSEQ_INSP_FIND_TEMP.sas7bdat" casout={caslib="hseq" name="HSEQ_INSP_FIND_TEMP1"};
quit;

Out of the 2 hours, how much of that time is the actual load step?

And are all of the 136 columns needed? Can you drop any?

Re: load dataset from client to CAS

SASKiwi — Mon, 07 Nov 2022 02:13:39 GMT

I strongly suspect that the poor performance is caused by large default character column lengths being defined on the SAS dataset being read from SAP/BW. This results in the dataset size being much larger than it should be. In SAS 9.4 data libraries on disk this is not a problem as you can apply the SAS option COMPRESS to get rid of the extra space efficiently. It is advisable to not compress in-memory tables as it adversely affects performance. I know it is tedious but you can resize all long character columns using the LENGTH statement to reduce space in the on-disk dataset which should improve load times into memory a lot.

Re: load dataset from client to CAS

dipand — Mon, 07 Nov 2022 06:13:55 GMT

my customer don't want that delete any columns, but i try your istruction. Thank you so much