DATA Step, Macro, Functions and more

I need user login and logout time for every day.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

I need user login and logout time for every day.

[ Edited ]

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.


Accepted Solutions
Solution
Sunday
Contributor
Posts: 36

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

[ Edited ]
/** 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.

 

 

 

PS

View solution in original post


All Replies
Super User
Posts: 5,914

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

If last action = logout each day just simply use min() and max() in SQL.
Data never sleeps
Occasional Contributor
Posts: 6

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

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;

 

 

Contributor
Posts: 36

Re: I need user login and logout time 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
  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 

PS
Occasional Contributor
Posts: 6

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

Posted in reply to shahparth260

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

 

 

Solution
Sunday
Contributor
Posts: 36

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

[ Edited ]
/** 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.

 

 

 

PS
Regular Contributor
Posts: 226

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


@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.

Contributor
Posts: 36

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

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;
 
  
 
PS
Occasional Contributor
Posts: 6

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

Posted in reply to shahparth260

Thanks for reply....

but in my data date, time, and users are changing 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;

 

 

Occasional Contributor
Posts: 6

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

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

Contributor
Posts: 36

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

[ Edited ]

/** 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;
PS
Regular Contributor
Posts: 226

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

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;
Occasional Contributor
Posts: 6

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

Posted in reply to error_prone

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

Contributor
Posts: 36

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

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.

PS
☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 154 views
  • 2 likes
  • 4 in conversation