DATA Step, Macro, Functions and more

Need to count number of the session by using retain, (please help I already spend 3 days on it :( )

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Need to count number of the session by using retain, (please help I already spend 3 days on it :( )

Hi guys, 

 

I have a dataset called T1 its attached 

 

I want to count the number of the session each person does. "dt" is the time that they visit the website and "User_Catg_ID" is their ID.  

once they visit the website their session is one, if they are inactive for more than 30 min and again active, the session should be increased by 1. 

for example :

4.PNG


My code:I wrote the code but it does not work properly! could you please help me to fix this issue Smiley Sad

 123.PNG


Accepted Solutions
Solution
3 weeks ago
Occasional Contributor
Posts: 11

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Hi Guys, 

 

Thanks alot for the reply, 

I  tried below code and it works, I also will use your codes! Thanks again Smiley Happy

 

data h.Imp_Rev_x2;
set h.Imp_Rev_x1;
retain session;
by User_Catg_ID;
retain dt_r;
if first.User_Catg_ID then session=1;
if first.User_Catg_ID then dt_r = dt;
dt_diff = (dt-dt_r)/60;
if dt_diff > 30 then session=session+1;
if dt_diff <= 30 then session=session;
dt_r = dt;
if last.User_Catg_ID=1 then user_last_pageview = 1;
run;

 

 

 

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Posted in reply to Hassankalantari
Can you please share the sample format in which you need the final dataset to look like?
PROC Star
Posts: 1,215

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Posted in reply to Hassankalantari

like this?

 

data have;
input dt :time5. User_Catg_ID $;
format dt time5.;
datalines;
18:30 555
18:31 555
18:35 555
19:45 555
19:47 555
20:02 985
20:03 985
20:05 985
;

proc sort data=have presorted;
   by User_Catg_ID dt;
run;

data want;
   set have;
   retain session;
   by User_Catg_ID;
   if not first.User_Catg_ID and dif(dt) > 30*60 then session+1;
   if first.User_Catg_ID then session=1;
run;
New Contributor
Posts: 3

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

[ Edited ]
Posted in reply to Hassankalantari

Try this:-

 data h.t2;
    set h.t1;
    by User_Catg_ID;
    retain session;
    retain dt_r;
    if first.User_Catg_ID then session=1;
    else
        do;
	    dt_diff = (dt-dt_r)/60;
	    if dt_diff > 30 then session=session+1;
	end;
    dt_r=dt;
    if last.User_Catg_ID then
        do;
            user_last_pageview=session;
            drop dt_r dt_diff session;
            output;
        end;
run;
Solution
3 weeks ago
Occasional Contributor
Posts: 11

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Hi Guys, 

 

Thanks alot for the reply, 

I  tried below code and it works, I also will use your codes! Thanks again Smiley Happy

 

data h.Imp_Rev_x2;
set h.Imp_Rev_x1;
retain session;
by User_Catg_ID;
retain dt_r;
if first.User_Catg_ID then session=1;
if first.User_Catg_ID then dt_r = dt;
dt_diff = (dt-dt_r)/60;
if dt_diff > 30 then session=session+1;
if dt_diff <= 30 then session=session;
dt_r = dt;
if last.User_Catg_ID=1 then user_last_pageview = 1;
run;

 

 

 

Occasional Contributor
Posts: 15

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Please try this code and refer to Revised_Session_Count column. Please confirm if it helps.

 

FILENAME F1 '/folders/myfolders/sasuser.v94/flat files/Session Calc.xlsx';

PROC IMPORT DATAFILE=F1
OUT=TEMP_T
DBMS=XLSX
REPLACE;

DATA TEMP;
SET TEMP_T;
FORMAT DT_NEW datetime.;
KEEP User_Catg_ID session_T DT_New;* D_dt date;

DT_New=INT(INPUT(DT,datetime.));
*INFORMAT DT datetime16.; /*INFORMAT statement couldn't read DT as datetime value. Not sure why. SO used INPUT() with same informat and it worked just fine*/

RENAME DT_NEW=DT;
RUN;

 

/*----Deleting TEMP_T dataset---*/
PROC DATASETS NOLIST LIB=work;
DELETE TEMP_T;
RUN;


PROC SORT DATA=TEMP;
BY User_Catg_ID DT;

DATA TEMP_1;
SET TEMP;
BY User_Catg_ID;

Prev_val=LAG(DT);

IF NOT FIRST.User_Catg_ID THEN TIME_DIFF=DT-Prev_val; /*NOTE: Using LAG(DT) directly in place of Prev_Val returns some weird results in few scenarios*/
ELSE TIME_DIFF=0;

RETAIN Revised_Session_Count 1;

IF FIRST.User_Catg_ID THEN Revised_Session_Count=1;
ELSE IF TIME_DIFF > (30*60) and NOT FIRST.User_Catg_ID THEN Revised_Session_Count+1;


DROP Prev_Val;
RUN;

PROC PRINT DATA=TEMP_1;
RUN;

Occasional Contributor
Posts: 11

Re: Need to count number of the session by using retain, (please help I already spend 3 days on it :

Posted in reply to mahesh146

Great!

I dont have the data now, but I will try and let you know the result!!

Thanks alot!

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 106 views
  • 0 likes
  • 4 in conversation