BookmarkSubscribeRSS Feed
DavidLie
Obsidian | Level 7

Hi all,

 

I hope you are doing well. I have a question regarding how to extract observations before and after a dummy.

 

This is what I have.

 

ID Var1 Var2 Dummy
1 20 41 0
1 17 20 0
1 97 68 0
1 93 49 0
1 91 77 1
1 51 37 0
1 88 55 0
1 42 14 0
2 62 40 0
2 93 46 0
2 77 70 0
2 31 32 0
2 53 58 1
2 21 73 0
2 55 94 0
2 73 29 0
2 34 27 0

 

What I need is to extract observation before, during, and after dummy variable indicating 1 (i.e., the text in red). So, the "want" dataset will be as below:

 

A B C Dummy
1 93 49 0
1 91 77 1
1 51 37 0
2 31 32 0
2 53 58 1
2 21 73 0

 

Thanks!


Best,
David

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

Try this

 

data have;
input ID Var1 Var2 Dummy;
datalines;
1 20 41 0
1 17 20 0
1 97 68 0
1 93 49 0
1 91 77 1
1 51 37 0
1 88 55 0
1 42 14 0
2 62 40 0
2 93 46 0
2 77 70 0
2 31 32 0
2 53 58 1
2 21 73 0
2 55 94 0
2 73 29 0
2 34 27 0
;

data want;
   merge have
         have(firstobs = 2 keep = dummy id rename = (dummy = lead_d id = lead_id));

   lag_d  = lag(dummy);
   lag_id = lag(id);

   if sum(dummy, lag_d, lead_d) and id = lag_id and id = lead_id;

   drop lead_: lag_:;
run;
yabwon
Amethyst | Level 16

what about those little ugly ones (first or last in the group):

3 62 40 1
3 93 46 0
3 77 70 0
4 62 40 0
4 93 46 0
4 77 70 1

? 😉 😉

 

BTW @DavidLie Could we have such situation? And what about multiple dummy in one group?

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



yabwon
Amethyst | Level 16
data have;
input D	Var1	Var2	Dummy;
cards;
1	20	41	0
1	17	20	0
1	97	68	0
1	93	49	0
1	91	77	1
1	51	37	0
1	88	55	0
1	42	14	0
2	62	40	0
2	93	46	0
2	77	70	0
2	31	32	0
2	53	58	1
2	21	73	0
2	55	94	0
2	73	29	0
2	34	27	0
;
run;
proc print;
run;

data want;
  do _N_ = 1 by 1 until(last.d);
    set have curobs=curobs;
    by d;

    if Dummy then CO=curobs;
  end;

  do _N_ = 1 to _N_;
    set have curobs=curobs2;

    if CO-1 <= curobs2 <= CO+1 then output;
  end;
run;

proc print;
run;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Tom
Super User Tom
Super User

You should be able to use the FIRST./LAST. flags to do this.

data want;
  set have ;
  by id dummy notsorted;
  if dummy then output;
  else if (first.dummy and not first.id) then output;
  else if (last.dummy and not last.id) then output;
run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1184 views
  • 0 likes
  • 4 in conversation