topic Re: split a large data in SAS Data Science

split a large data

jojo — Sun, 23 Sep 2012 22:11:26 GMT

I need to split one dataset to two. If the value for each of the two variables (A, B) is zero for all obs in the by group, then output to one dataset, otherwise output to another dataset. So all obs with x=2, y= 12 or x=2, y=13 or x=3, y=9 should be in one dataset and other obs should be in another dataset.

I can do it by getting the sum of A and B within the group and output the last.m if the sum of A and B are zero, and then merge back with the original data by n and m to get all obs. I am thinking if there is another way to do this as the data are huge (over several millions obs), I really want to avoid sorting or merging as much as possible.

x y A B

2 12 0 0

2 13 0 0

2 14 0 0

2 14 1 0

2 14 0 0

2 14 0 1

2 15 0 0

2 15 0 1

2 16 0 0

2 16 0 1

3 9 0 0

Re: split a large data

TomKari — Mon, 24 Sep 2012 00:06:10 GMT

Here's a non-optimized version using SQL. If you're running this on a server, I don't think it'll be slow with millions of observations. On your PC it might be slow. I know it could be combined into one SQL statement, but I don't know that it'll make it any faster.

It's a start.

Tom

data have;
input x y A B;
cards;
2 12 0 0
2 12 0 0
2 12 0 0
2 12 0 0
2 12 0 0
2 13 0 0
2 13 0 0
2 13 0 0
2 13 0 0
2 14 0 0
2 14 1 0
2 14 0 0
2 14 0 1
2 15 0 0
2 15 0 0
2 15 0 1
2 15 0 1
2 16 0 0
2 16 0 1
3 9 0 0
3 9 0 0
3 9 0 0
run;

proc sql;
create table have_totals as
select x, y, sum(a) as sum_A, sum(b) as sum_B
from have
group by x, y;

create table want_0 as
select h.x, h.y, h.A, h.B
from have h join have_totals t on h.x = t.x and h.y = t.y
where t.sum_A = 0 and t.sum_B = 0;

create table want_gt_0 as
select h.x, h.y, h.A, h.B
from have h join have_totals t on h.x = t.x and h.y = t.y
where t.sum_A > 0 or t.sum_B > 0;
quit;

Re: split a large data

Patrick — Mon, 24 Sep 2012 08:25:38 GMT

If the set of distinct keys fits into memory then below code could be an option:

data wantAllZero wantNotAllZero;

set have;

drop _: ;

if _n_=1 then

do;

dcl hash h1 (dataset:'have(where=(a ne 0 or b ne 0))',hashexp:9);

_rc=h1.defineKey('x','y');

_rc=h1.defineDone();

end;

if h1.check()=0 then output wantNOtAllZero;

else output wantAllZero;

run;

Message was edited by: Patrick Matter Code Fix: added closing bracket in where clause

Re: split a large data

jojo — Mon, 24 Sep 2012 18:03:32 GMT

Thank you!

Re: split a large data

jojo — Mon, 24 Sep 2012 18:04:04 GMT

Thank you very much!

Re: split a large data

Tom — Mon, 24 Sep 2012 19:04:36 GMT

data wantAllZero wantNotAllZero;

nonzero=0;

do until (last.y);

set have;

by x y;

if a or b then nonzero=1;

end;

do until(last.y);

set have;

by x y;

if nonzero then output wantNotAllZero;

else output wantAllZero;

end;

drop nonzero;

run;

Re: split a large data

Haikuo — Mon, 24 Sep 2012 20:38:37 GMT

How about using Merge statement?

data data wantAllZero wantNotAllZero;

merge have (where=(a ne 0 or b ne 0) in=no ) have;

by x y;

if no then output wantnotallzero;

else output wantallzero;

run;

Haikuo

topic Re: split a large data in SAS Data Science

split a large data

Search Results by Yahoo!

Re: split a large data

Re: split a large data

Re: split a large data

Re: split a large data

Re: split a large data

Re: split a large data