It's creating new variables with the imputed results:   ... View more

I can replicate this behavior except I get 100009 rows (usually):   NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 200000 observations read from the table TEST in caslib CASUSER. NOTE: The table out in caslib CASUSER has 100009 observations and 7 variables.   I think what is happening here is a timing issue.  I updated the hash definition to include the threadid and hostname both when a row is added to the hash and when it is replaced:   data _null_; length hostname hostname_add $50; if _n_=1 then do; dcl hash h(ordered:"A"); h.defineKey("id"); h.defineData("id","var","var_c", "threadid_add", "threadid_replace", "hostname_add", "hostname_replace"); h.defineDone(); call missing(threadid, threadid_add, hostname, hostname_add); end; set casuser.test end=lr; *by id; if h.find()=0 then do; var=sum(var,var_n); threadid_replace = _threadid_; hostname_replace = _hostname_; h.replace(); end; else do; var=var_n; threadid_add = _threadid_; hostname_add = _hostname_; h.add(); end; if lr then h.output(dataset:'out'); run; data dup_id_rows; set casuser.out; by id; if not (first.id and last.id); run;   The dup_id_rows dataset contains the 18 rows associated with the 9 "extra" ids.  In each case, the id was added to the hash twice by a separate thread and never replaced.     There is no locking of the hash table going on behind the scenes, so I think these are instances where the second thread checked the hash before the first thread's add was completed.   The issue is not as pronounced as it could be, in this particular case, because the casuser.test table is not distributed across all the worker nodes.  When it was initially created, the log said: NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step has no input data set and will run in a single thread.   It can be distributed by running table.partition: proc cas; table.partition / casout={caslib="casuser", name="test", replace=true} table={caslib="casuser", name="test"} ; table.tabledetails / caslib="casuser" name="test" level="node"; table.tableinfo / table="test"; quit;   My system has 4 workers, so the output of table.tabledetails now shows 50k rows on each node instead of 200k on one node.  When I run the hash DATA step there are now 100088 rows due to more threads at work:   NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 200000 observations read from the table TEST in caslib CASUSER. NOTE: The table out in caslib CASUSER has 100088 observations and 7 variables.   To avoid this and still run multi-threaded, we can make all the rows associated with a given ID be processed by the same thread.  This can be done by adding BY ID; to the DATA step.  I have it in the above code but commented out.  When I uncomment and run, there are 100000 rows:   NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 200000 observations read from the table TEST in caslib CASUSER. NOTE: The table out in caslib CASUSER has 100000 observations and 7 variables.   ... View more

Should the values of VAR_B in the last 4 rows of the initial data table match those in your "want" table?   If so, then the only way I could think to do this (other than transposing with the DATA step) is to add an additional SEQ_CNT variable for proc transpose to use as a BY variable. data table; input ID $ ACC_DATE SEQUENCE VAR_A $ VAR_B ; datalines; 1 202201 1 STEP_B 350 1 202201 2 STEP_B 500 1 202201 3 STEP_B 700 2 202201 1 STEP_A 20 2 202201 1 STEP_B 5 2 202201 2 STEP_A 40 2 202201 2 STEP_B 10 2 202201 3 STEP_A 80 2 202201 3 STEP_B 10 ; run; PROC SORT DATA= TABLE FORCE; BY ID ACC_DATE SEQUENCE; RUN; * create SEQ_CNT variable; data table; set table; by ID ACC_DATE SEQUENCE; if first.SEQUENCE then SEQ_CNT=0; SEQ_CNT+1; run; PROC SORT DATA= TABLE FORCE; BY ID SEQ_CNT; RUN; proc transpose data=work.Table out=work.Table_T1 (drop=SEQ_CNT) name=VARIABLE PREFIX=_ DELIM=_ let; by ID SEQ_CNT; var var_a var_b; id ACC_DATE SEQUENCE; run; proc sort data=Table_T1; by ID VARIABLE; run; ... View more

I cannot claim any expertise with this, but I noticed the doc for the doESP= parm says "when set to True, the action can take advantage of partitioning and ordering on the input table. You must specify the groupBy parameter for the input table. The ID variable must be specified as the last groupBy parameter and in the orderBy parameter."   So I think your table just needs to be partitioned prior to the aggregation.  This worked for me: data casuser.PRICEDATA; set SASHELP.PRICEDATA; run; proc cas; table.partition / casout={caslib="CASUSER",name="PRICEDATA", replace=true}, table={name="PRICEDATA",caslib="casuser", groupby={{name="regionname"}} orderby={{name="date"}} }; run; aggregation.aggregate / table={name="PRICEDATA",caslib="casuser", where="1=1", groupby={ {name="regionname"}, {name="date" , format="yyq6."}} } varspecs={ {subset="sum", name="sale", columnnames="sum_sale"} } casout={name="nodup2", caslib='casuser', replace=TRUE} savegroupbyraw=TRUE, savegroupbyformat=false, raw=TRUE, windowInt="quarter", contribute="productname", keepRecord=TRUE, doESP=TRUE, interval="quarter", jumpingWindow=false, id="date" ; table.fetch / table={name="nodup2", caslib="casuser"}; run; quit; ... View more

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? ... View more

You can create an id column with the names you want.    proc cas; transpose.transpose / table={caslib="casuser", name="IRIS2", groupBy="Species", computedVars={name="colname"} computedVarsProgram="colname='COL1';"}, id="colname", name="Variable", casOut={name="IRIS4", replace=TRUE, caslib="casuser"} ; quit; ... View more

I think fileInfo can be used.    caslib myCaslib datasource=(srctype="path") path="&path"; proc cas; table.fileInfo result=r / caslib="myCaslib"; filelist = r.FileInfo[,"Name"]; if (filelist == "my_file.txt") then print "my_file.txt exists"; quit; ... View more