BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shasank
Quartz | Level 8

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.  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
novinosrin
Tourmaline | Level 20

do you mean:

 

data want;

set have;

if var1=1 then output true;

else output false:

run;

 

Reeza
Super User

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

novinosrin
Tourmaline | Level 20

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

Astounding
PROC Star

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;

shasank
Quartz | Level 8
Thank you for your help @Astounding. You are awesome.
novinosrin
Tourmaline | Level 20

why does the second output dataset example include-

 

ID     Var1

 

123          1

123          1

789          1

789          1

789          0  /* is this correct?*/

shasank
Quartz | Level 8
@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.
novinosrin
Tourmaline | Level 20

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;

shasank
Quartz | Level 8
It worked. I appreciate your help. Thank you very much.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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