topic Re: Split one dataset into two evenly but with a catch. in SAS Enterprise Guide

Split one dataset into two evenly but with a catch.

sdang — Thu, 20 Apr 2017 23:47:01 GMT

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.

Please help!

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

Quentin — Fri, 21 Apr 2017 00:46:00 GMT

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;

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

kiranv_ — Fri, 21 Apr 2017 01:37:58 GMT

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;

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

PGStats — Fri, 21 Apr 2017 04:18:47 GMT

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;

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

Ksharp — Fri, 21 Apr 2017 03:16:33 GMT

@PGStats

You need

retain set1;

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

PGStats — Fri, 21 Apr 2017 04:19:33 GMT

Right! Thanks @Ksharp.