BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ajitesh
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
shahparth260
Quartz | Level 8
/** 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

14 REPLIES 14
LinusH
Tourmaline | Level 20
If last action = logout each day just simply use min() and max() in SQL.
Data never sleeps
Ajitesh
Calcite | Level 5

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;

 

 

shahparth260
Quartz | Level 8
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
Ajitesh
Calcite | Level 5

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

 

 

shahparth260
Quartz | Level 8
/** 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
error_prone
Barite | Level 11

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

shahparth260
Quartz | Level 8
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
Ajitesh
Calcite | Level 5

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;

 

 

Ajitesh
Calcite | Level 5

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

shahparth260
Quartz | Level 8

/** 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
error_prone
Barite | Level 11

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;
Ajitesh
Calcite | Level 5

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

shahparth260
Quartz | Level 8

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
Strawberry
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1916 views
  • 3 likes
  • 5 in conversation