topic Retain records after a specifeid row in SAS Programming

Retain records after a specifeid row

Shoref_Rahman — Mon, 18 Feb 2019 11:29:51 GMT

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 .

Re: Retain records after a specifeid row

RW9 — Mon, 18 Feb 2019 11:40:18 GMT

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.

Re: Retain records after a specifeid row

FredrikE — Mon, 18 Feb 2019 11:45:42 GMT

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

Re: Retain records after a specifeid row

Shoref_Rahman — Mon, 18 Feb 2019 12:25:49 GMT

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;

Re: Retain records after a specifeid row

Shoref_Rahman — Mon, 18 Feb 2019 12:26:35 GMT

THank you for your help!!