SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Split dataset into 2 using a binary variable

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Split dataset into 2 using a binary variable

[ Edited ]

Hello SAS Community,

 

I need your help for the following problem.

 

Dataset1;

ID         Var1

123          0

123          0

123          1

123          1

456           0

456           0

456           0

456           0

789           0

789           1

789           1

789           0

 

The output datasets should look like

ID     Var1

123         0

123         0 

789         0

 

ID     Var1

 

123          1

123          1

789          1

789          1

789          0

 

Logic:

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

 


Accepted Solutions
Solution
a week ago
Super User
Posts: 5,610

Re: Split dataset into 2 using a binary variable

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;

end;

drop total_1 output_flag;

run;

View solution in original post


All Replies
PROC Star
Posts: 357

Re: Split dataset into 2 using a binary variable

do you mean:

 

data want;

set have;

if var1=1 then output true;

else output false:

run;

 

Super User
Posts: 20,224

Re: Split dataset into 2 using a binary variable

Posted in reply to novinosrin

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

PROC Star
Posts: 357

Re: Split dataset into 2 using a binary variable

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

Solution
a week ago
Super User
Posts: 5,610

Re: Split dataset into 2 using a binary variable

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;

end;

drop total_1 output_flag;

run;

Contributor
Posts: 20

Re: Split dataset into 2 using a binary variable

Posted in reply to Astounding
Thank you for your help @Astounding. You are awesome.
PROC Star
Posts: 357

Re: Split dataset into 2 using a binary variable

why does the second output dataset example include-

 

ID     Var1

 

123          1

123          1

789          1

789          1

789          0  /* is this correct?*/

Contributor
Posts: 20

Re: Split dataset into 2 using a binary variable

Posted in reply to novinosrin
@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.
PROC Star
Posts: 357

Re: Split dataset into 2 using a binary variable

[ Edited ]

data have;

input ID         Var1;

datalines;

123          0

123          0

123          1

123          1

456           0

456           0

456           0

456           0

789           0

789           1

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;

end;

drop temp:;

run;

Contributor
Posts: 20

Re: Split dataset into 2 using a binary variable

Posted in reply to novinosrin
It worked. I appreciate your help. Thank you very much.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 165 views
  • 3 likes
  • 4 in conversation