## Split one dataset into two evenly but with a catch.

Solved
Frequent Contributor
Posts: 106

# Split one dataset into two evenly but with a catch.

I want to have two datasets split evenly from a much larger dataset but before the split, SAS must not split obersvations that are in same zipcodes (criteria).  We want to mail an food offer from a dataset we created but  with two different valid dates.  The first valid date is assigned to the first splited dataset and second valid dates assigned to the second splited dataset.   However, we don't want to separate married couple who want to dine together.  Our manual logic is to sort by zip codes and split the datasets evenly and right before the split make sure no two address are the same.  Below is what our rows of data look likes.

 ID FN LN Address City State state ZIP 1111111 Wayne Smith 111 Left St BROOKLYN NY 11234 1111112 Mary Smith 111 left BROOKLYN NY 11234

A dataset with 10,000 rows split evenly:

Dataset 1 = 5,000

Dataset 2 = 5,000

But don't separate same zip codes.

Accepted Solutions
Solution
‎12-12-2017 11:57 AM
PROC Star
Posts: 1,471

## Re: Split one dataset into two evenly but with a catch.

If you're only looking for a rough split, you could just test to find the midway point, and then split the file at the first zipcode change after that.  Something like:

data have;
do id=1 to 10000;
zip=ceil(ranuni(0)*20);
output;
end;
run;

proc sort data=have;
by zip;
run;

data file1 file2;
set have nobs=n;
by zip;

retain mid 0;
if _n_ > (n/2) and first.zip then mid=1;

if mid=0 then output file1;
else output file2;

drop mid; run;

All Replies
Solution
‎12-12-2017 11:57 AM
PROC Star
Posts: 1,471

## Re: Split one dataset into two evenly but with a catch.

If you're only looking for a rough split, you could just test to find the midway point, and then split the file at the first zipcode change after that.  Something like:

data have;
do id=1 to 10000;
zip=ceil(ranuni(0)*20);
output;
end;
run;

proc sort data=have;
by zip;
run;

data file1 file2;
set have nobs=n;
by zip;

retain mid 0;
if _n_ > (n/2) and first.zip then mid=1;

if mid=0 then output file1;
else output file2;

drop mid; run;
PROC Star
Posts: 549

## Re: Split one dataset into two evenly but with a catch.

[ Edited ]

This might work. i have not tested code completly

/*gets into your half be careful here you can get decimals if count is odd*/
proc sql;
select count(*)/ 2 into :halfnum from intial_table;
quit;

/*gets you same zipcode  be careful zipcode is not right one probably you should pick addres*/
proc sql;
create table samezipcode_firsthalf
select * from intial_table
where zipped in
(select zipped from intialtable
group by name
having count(*) gt 1) ;
quit;

/*create another table without same zipcode records as id seems unqique*/

proc sql;

create newintialtable as

select * from intialtable where

id not in (select id from samezipcode_firsthalf);

quit;

/* picking up remaining records for first dataset*/
proc sql;
select count(*) into :newtot
from amezipcode_firsthalf;
quit;

proc sql;
insert into samezipcode_firsthalf
select * from newintialtable /* pick up new records from new intial table */
where monotonic() le %eval( %left(&halfnum)-%left(&newtot));
quit;

/* id is unique as per your data*/
proc sql;
create table second as
select * from intial_table
where id not in (select id from samezipcode_firsthalf);
quit;

Posts: 5,624

## Re: Split one dataset into two evenly but with a catch.

[ Edited ]

Another way to do a rough split:

proc sort data=have; by zip; run;

data set1 set2;
retain set1;
set have; by zip;
if first.zip then set1 = not set1;
if set1 then output set1; else output set2;
drop set1;
run;
PG
Super User
Posts: 10,850

@PGStats

You need

retain set1;

?

Posts: 5,624

## Re: Split one dataset into two evenly but with a catch.

Right! Thanks @Ksharp.

PG
☑ This topic is solved.