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

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

Hassankalantari — Wed, 30 May 2018 04:02:32 GMT

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 :

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

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

mahesh146 — Wed, 30 May 2018 05:41:46 GMT

Can you please share the sample format in which you need the final dataset to look like?

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

PeterClemmensen — Wed, 30 May 2018 05:43:44 GMT

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;

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

Nartlada — Wed, 30 May 2018 05:48:29 GMT

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;

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

Hassankalantari — Wed, 30 May 2018 06:04:17 GMT

Hi Guys,

Thanks alot for the reply,

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

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;

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

mahesh146 — Wed, 30 May 2018 06:46:49 GMT

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;

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

Hassankalantari — Wed, 30 May 2018 07:59:30 GMT

Great!

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

Thanks alot!