topic Re: Split dataset into 2 using a binary variable in SAS Data Management

Split dataset into 2 using a binary variable

shasank — Mon, 04 Dec 2017 20:15:33 GMT

Hello SAS Community,

I need your help for the following problem.

Dataset1;

ID Var1

123 0

123 1

456 0

789 0

789 1

789 0

The output datasets should look like

ID Var1

123 0

789 0

ID Var1

123 1

789 1

789 0

Logic:

1) Include only if the ID has at least one occurrence of 1 under var1.

Re: Split dataset into 2 using a binary variable

novinosrin — Mon, 04 Dec 2017 20:03:51 GMT

do you mean:

data want;

set have;

if var1=1 then output true;

else output false:

run;

Re: Split dataset into 2 using a binary variable

Reeza — Mon, 04 Dec 2017 20:05:25 GMT

@novinosrin ID 456 is not included, so not quite.

Re: Split dataset into 2 using a binary variable

novinosrin — Mon, 04 Dec 2017 20:10:50 GMT

@Reeza Lol so sorry. You are right. I haven't had my lunch yet. Too much of coffee makes me giddy 🙂

Re: Split dataset into 2 using a binary variable

Astounding — Mon, 04 Dec 2017 20:30:50 GMT

Inferring some unwritten rules, based on the specified results:

data before_1 following_1;

total_1 = 0;

do until (last.id);

set have;

by id;

total_1 + var1;

end;

if total_1 then output_flag='Y';

total_1=0;

do until (last.id);

set have;

by id;

total_1 + var1;

output_flag='Y' then do;

if total_1=0 then output before_1;

else output following_1;

end;

drop total_1 output_flag;

run;

Re: Split dataset into 2 using a binary variable

novinosrin — Mon, 04 Dec 2017 20:40:14 GMT

why does the second output dataset example include-

ID Var1

123 1

789 1

789 0 /* is this correct?*/

Re: Split dataset into 2 using a binary variable

shasank — Mon, 04 Dec 2017 20:45:18 GMT

@novinosrin Yes, The pointer should start at the 1st iteration of 1. its pre-occurrence of 1 and post-occurrence of 1 for each ID. So, the pre-occurrence of 1 and post occurrence of 1 are expected to be in separate data sets. Thanks for your help.

Re: Split dataset into 2 using a binary variable

shasank — Mon, 04 Dec 2017 20:52:39 GMT

Thank you for your help @Astounding. You are awesome.

Re: Split dataset into 2 using a binary variable

novinosrin — Mon, 04 Dec 2017 20:56:29 GMT

data have;

input ID Var1;

datalines;

123 0

123 1

456 0

789 0

789 1

789 0

;

data true false;

call missing(temp);

do until(last.id);

set have;

by id;

if var1 then temp=var1;

end;

do until(last.id);

set have;

by id;

if temp then do;

if var1 then temp1=var1;

if temp1 then output true;

else output false;

end;

drop temp:;

run;

Re: Split dataset into 2 using a binary variable

shasank — Mon, 04 Dec 2017 21:10:09 GMT

It worked. I appreciate your help. Thank you very much.