Retain records after a specifeid row

[Shoref_Rahman]

Hi,

I am currently working with some Website URLs, I want to identify what URL people click on after visiting a particular page.

At the moment I have set up a flag that indicates what URL is the start point. I want to retain only the following two URLS after this start point.

Action Number            URL                                                                                                      HOMEPAGE_FLAG

0                                  http://bg.active/dmg/index_prime.htm .
1                                  http://bg.active/ocelot/dmg/dmg90024/v31/p                                                          ...  1
2                                  http://bg.active/ocelot/dmg/dmg90021/v29/p .
3                                  http://bg.active/ocelot/dmg/dmg90021/v29/p/12 .   
4                                  http://bg.active/ocelot/dmg/dmg90021/v29/p .
5                                  http://bg.active/cms/background_case/viewer/viewer.htm?c=dmg30070 .

above is an example of the table i am working with. Actions numbers indicate number of actions/clicks./pages, URL is where they visited and the flag at the end is the the start point i am interested in.

I want to produce a table that retains the start point and the two URLS following the start point.

Action Number             URL                                                                                              HOMEPAGE FLAG

1                                  http://bg.active/ocelot/dmg/dmg90024/v31/p                                                          ...  1
2                                  http://bg.active/ocelot/dmg/dmg90021/v29/p .
3                                  http://bg.active/ocelot/dmg/dmg90021/v29/p/12 .   

[RW9 · Accepted solution]

At a quick, untested guess:

data want;
  set have;
  if lag(homepage_flag) or lag2(homepage_flag) then output;
run;

/* or */
data want;
  set have;
  retain cnt;
  if homepage_flag=1 then cnt=0;
  if cnt > . then cnt=cnt+1;
  if cnt in (1,2) then output;
run;

Various ways of doing this really.

[Shoref_Rahman]

This worked a treat, thank you for a quick response - I am using the first exmaple but have changed it a bit so it now looks like this,

data want;
set have;
if HOMEPAGE_FLAG = 1 or lag(HOMEPAGE_flag) or lag2(HOMEPAGE_flag) then output;
run;

[FredrikE]

Here is another untested version 🙂

proc sql;
 create table want as
 	select * from have as have
  	inner join (select actionNumber from have where homepage_flag=1) as have2
  	on have2.actionNumber <= have.actionNumber <= have2.actionNumber + 2
  ;
quit;

//Fredrik

[Shoref_Rahman]

THank you for your help!!