topic Re: I need user login and logout time for every day. in SAS Programming

I need user login and logout time for every day.

Ajitesh — Sat, 11 Aug 2018 07:28:21 GMT

I have a data like-

data test;

input Date Action_Time User_Name

cards;

1-Jul-18 8:00 Ankit

1-Jul-18 9:00 Ankit

1-Jul-18 9:30 Ankit

.................

................

1-Jul18 5:30 Ankit

;

run;

i want result for every user- User_Name login_date login_time logout_time.

Ankit 1-Jul-18 8:00 am 5:30 pm

Rekha 1-Jul-18 8:30 am 5:00 pm

Please help me with solution. Thanks in advance.

Re: I need user login and logout time for every day.

LinusH — Sat, 11 Aug 2018 07:38:22 GMT

If last action = logout each day just simply use min() and max() in SQL.

Re: I need user login and logout time for every day.

shahparth260 — Sat, 11 Aug 2018 07:50:23 GMT

data x;
  infile datalines;
  input date $14. time $8.  user $10.;
  datalines;
  18june2018    9:00   user1
  18june2018    13:00  user1
  18june2018    10:40  user1
  18june2018    23:40  user1
  18june2018    13:40  user1
      ;
run;
please refer this logic same scenario 
data x1;
 set x;
 new_time=input(time,hhmmss4.);
 drop time;
run;

proc sort data=x1;
 by date user new_time;
 run;
 
data x2;
   format logon  hhmm10.;
   set x1;
   retain logon ;
   by date user;
   if first.user then logon=new_time;
   rename new_time=logoff;
   if last.user;
  run;
  
 data x3;
  set x2;
  format logon logoff hhmm10.;
 run;

Re: I need user login and logout time for every day.

Ajitesh — Sat, 11 Aug 2018 07:57:39 GMT

But my data have multiple user's alerts performance with date and time like this-

data test;
format date date9. Action_time time10.;
input Date date9. Action_Time time10. User_Name$20.;
cards;
1-Jul-18 8:00:00 am Ankit
1-Jul-18 9:00:00 am Ankit
1-Jul-18 9:30:00 am Ankit
2-Jul-18 9:30:00 am Ankit
2-Jul-18 9:45:00 am Ankit
1-Jul-18 9:00:00 am Rekha
1-Jul-18 9:30:00 am Rekha
1-Jul-18 9:30:00 am Rekha
2-Jul-18 6:30:00 pm Rekha
2-Jul-18 6:40:00 pm Rekha

;
run;

Re: I need user login and logout time for every day.

Ajitesh — Sat, 11 Aug 2018 08:02:12 GMT

Thanks for reply....

but in my data date, time, and users are changing like this-

Re: I need user login and logout time for every day.

Ajitesh — Sat, 11 Aug 2018 08:03:31 GMT

I need login and logout time of every users for every day

Re: I need user login and logout time for every day.

shahparth260 — Sat, 11 Aug 2018 08:09:50 GMT

data x;
  infile datalines;
  input date $14. time $8.  user $10.;
  datalines;
  18jun2018    9:00   user1
  18jun2018    13:00  user1
  18jun2018    10:40  user1
  18jun2018    23:40  user1
  20jun2018    13:40  user1
  18jun2018    9:00   user1
  18jun2018    3:00   user2
  19jun2018    1:40   user2
  20jun2018    20:20  user2
  18jun2018    3:20   user2
  20jun2018    1:50   user3
  18jun2018    19:30  user3
  18jun2018    12:00  user3
  ;
run;

data x1;
 format new_date mmddyy10.;
 set x;
 new_time=input(time,hhmmss4.);
 new_date=input(date,date9.);
 drop time date;
run;

proc sort data=x1;
 by  user new_date  new_time;
 run;
 
data x2;
   format logon  hhmm10.;
   set x1;
   retain logon ;
   by user new_date  new_time ;
   if first.user then logon=new_time;
   rename new_time=logoff;
   if last.user;
  run;
  
 data x3;
  set x2;
  format logon logoff hhmm10.;
 run;

* I have update this but here's only thing I need to ask you, you want to get date as well I mean logon date and log off date because you were asking like it should be 1 record per subject but if you provide example then makes me more clear ! try it

Re: I need user login and logout time for every day.

Ajitesh — Sat, 11 Aug 2018 08:20:23 GMT

Thanks for quick reply.. this code is really good and working ..

but i need data date wise as well...

now i m getting result like this-

logon new_date user logoff

9:00 06/20/2018 user1 13:04
3:00 06/20/2018 user2 20:02
12:00 06/20/2018 user3 1:50

this is giving 20th june data only.. i need same result for a month like

logon new_date user logoff

9:00 06/20/2018 user1 13:04

8:30 06/21/2018 user1 5:30
3:00 06/20/2018 user2 20:02

8:30 06/21/2018 user2 5:00
12:00 06/20/2018 user3 1:50

8:30 06/21/2018 user3 5:50

Re: I need user login and logout time for every day.

shahparth260 — Sat, 11 Aug 2018 20:37:18 GMT


/** REFER THIS AS YOU ASKED "I need login and logout time of every users for every day"**/


data x;
  infile datalines;
  input date $14. time $8.  user $10.;
  datalines;
  18jun2018    9:00   user1
  18jun2018    13:00  user1
  18jun2018    10:40  user1
  18jun2018    23:40  user1
  20jun2018    13:40  user1
  20jun2018    14:39  user1
  18jun2018    9:00   user2
  18jun2018    3:00   user2
  19jun2018    1:40   user2
  20jun2018    20:20  user2
  18jun2018    3:20   user3
  20jun2018    1:50   user3
  18jun2018    19:30  user3
  18jun2018    12:00  user3
  ;
run;

data x1;
 set x;
 new_time=input(time,hhmmss4.);
 new_date=input(date,date9.);
run;

proc sort data=x1;
 by  user new_date ;
 run;
 
data x2;
 retain cnt;
 set x1;
 by user new_date;
 if first.user then cnt=1;
 else if first.new_date then cnt=cnt+1;
run;

proc sort data=x2;
 by user new_date cnt;
run;

 
data x3;
   format logon  hhmm10.;
   set x2;
   retain logon ;
   by user new_date  cnt;
   if first.cnt then logon=new_time;
   rename time=logoff;
   if last.cnt;
   drop cnt new_date new_time;
  run;

Re: I need user login and logout time for every day.

error_prone — Sun, 12 Aug 2018 04:46:27 GMT

@Ajitesh wrote:
But my data have multiple user's alerts performance with date and time like this-

data test;
format date date9. Action_time time10.;
input Date date9. Action_Time time10. User_Name$20.;
cards;
1-Jul-18 8:00:00 am Ankit
1-Jul-18 9:00:00 am Ankit
1-Jul-18 9:30:00 am Ankit
2-Jul-18 9:30:00 am Ankit
2-Jul-18 9:45:00 am Ankit
1-Jul-18 9:00:00 am Rekha
1-Jul-18 9:30:00 am Rekha
1-Jul-18 9:30:00 am Rekha
2-Jul-18 6:30:00 pm Rekha
2-Jul-18 6:40:00 pm Rekha

;
run;

Then you need something like "group by user, date" in proc SQL.

Re: I need user login and logout time for every day.

shahparth260 — Sun, 12 Aug 2018 07:49:51 GMT

/** REFER THIS AS YOU ASKED "I need login and logout time of every users for every day"**/data x;
  infile datalines;
  input date $14. time $8.  user $10.;
  datalines;
  18jun2018    9:00   user1
  18jun2018    13:00  user1
  18jun2018    10:40  user1
  18jun2018    23:40  user1
  20jun2018    13:40  user1
  20jun2018    14:39  user1
  18jun2018    9:00   user2
  18jun2018    3:00   user2
  19jun2018    1:40   user2
  20jun2018    20:20  user2
  18jun2018    3:20   user3
  20jun2018    1:50   user3
  18jun2018    19:30  user3
  18jun2018    12:00  user3
  ;
run;

data x1;
 set x;
 new_time=input(time,hhmmss4.);
 new_date=input(date,date9.);
run;

proc sort data=x1;
 by  user new_date ;
 run;
 
data x2;
 retain cnt;
 set x1;
 by user new_date;
 if first.user then cnt=1;
 else if first.new_date then cnt=cnt+1;
run;

proc sort data=x2;
 by user new_date cnt;
run;

 
data x3;
   format logon  hhmm10.;
   set x2;
   retain logon ;
   by user new_date  cnt;
   if first.cnt then logon=new_time;
   rename time=logoff;
   if last.cnt;
   drop cnt new_date new_time;
  run;

please check out very last code which i updated( message no 9 reply.) this should work as per your requirement.

Re: I need user login and logout time for every day.

error_prone — Sun, 12 Aug 2018 09:33:51 GMT

With proc sql the code is somewhat shorter:

data test;
	format date date9. Action_time time10.;
	input Date date9. Action_Time time10. User_Name$20.;
	cards;
1-Jul-18 8:00:00 am Ankit
1-Jul-18 9:00:00 am Ankit
1-Jul-18 9:30:00 am Ankit
2-Jul-18 9:30:00 am Ankit
2-Jul-18 9:45:00 am Ankit
1-Jul-18 9:00:00 am Rekha
1-Jul-18 9:30:00 am Rekha
1-Jul-18 9:30:00 am Rekha
2-Jul-18 6:30:00 pm Rekha
2-Jul-18 6:40:00 pm Rekha
;
run;

proc sql;
	select User_Name, Date, 
			min(Action_Time) as Login format=time., 
			max(Action_Time) as Logout format=time.
		from work.test 
			group by User_Name, Date;
quit;

Re: I need user login and logout time for every day.

Ajitesh — Sun, 12 Aug 2018 15:19:41 GMT

Thank you so much for your response. Codes are working fine and it gives same result. Proc sql code is good. Thanks.

Re: I need user login and logout time for every day.

shahparth260 — Sun, 12 Aug 2018 16:19:47 GMT

No, Problem.It depends upon your choice. I usually try to work with DATA steps only (even if proc sql is good facility to do task).

I use proc sql when I come across such as many to many merge or which data steps can't handle.

Re: I need user login and logout time for every day.

Strawberry — Sat, 25 Jul 2020 11:25:23 GMT

Hi Guys,

Can some one suggest me any sample code for pulling user login and logout information from SAS Metadata or from SAS Metadata logs, including the SAS tools he or she used