topic Re: SAS Splitting,Sorting,Merging Large dataset in SAS Programming

SAS Splitting,Sorting,Merging Large dataset

JJP1 — Tue, 21 May 2019 09:09:31 GMT

Hi ,

Would you please help on coding part for below steps please.please help

data _null_;
set xx.ZBB nobs=nobs; (dataset has 800+ millions of records)
call symputx('nobs',nobs);
run;
%do i=1 to 8;
*create sas code to sort nobs/8 at a time;
* run the sas code;
%end;
*create a wait code to ensure 8 datasets are created;
*run the following four datasteps in parallel;
data four1;
merge eight1-eight2;
by key;
run;
data four2;
merge eight3-eight4;
by key;
data four3;
merge eight5-eight6;
by key;
run;
data four4;
merge eight7-eight8;
by key;
run;

Re: SAS Splitting,Sorting,Merging Large dataset

Kurt_Bremser — Tue, 21 May 2019 11:49:24 GMT

data
  eight1
  eight2
  eight3
  eight4
  eight5
  eight6
  eight7
  eight8
;
set xx.zbb;
select (mod(_n_,8);
  when (1) output eight1;
  when (2) output eight2;
  when (3) output eight3;
  when (4) output eight4;
  when (5) output eight5;
  when (6) output eight6;
  when (7) output eight7;
  when (0) output eight8;
end;
run;

takes care of splitting in one step, without having to count at all.

Re: SAS Splitting,Sorting,Merging Large dataset

JJP1 — Tue, 21 May 2019 14:19:08 GMT

Thanks @Kurt_Bremser .

Re: SAS Splitting,Sorting,Merging Large dataset

Astounding — Tue, 21 May 2019 14:40:31 GMT

There are several items you need to address besides the splitting of the data.

If you plan to put all the data sets back together again as the final step, there is no advantage in creating FOUR1 FOUR2 FOUR3 and FOUR4. You might as well just combine all 8 subsets in one step.

MERGE is the wrong tool for the job. It is slower and does the wrong thing. Using MERGE explains the results you got that dropped a handful of observations. That result means that KEY is not unique. I don't know whether it is supposed to be unique or not, but there are some KEYs with more than 1 observation in your original data set. The right tool for the job would be SET instead of MERGE (but keep the BY statement in the program). If your boss told you to use MERGE, you need someone on the team with more SAS knowledge.