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.
/** 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.
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;
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
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
/** 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.
@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.
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;
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;
I need login and logout time of every users for every day
/** 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;
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;
Thank you so much for your response. Codes are working fine and it gives same result. Proc sql code is good. Thanks.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.