topic Re: Select first login only per day in SAS Procedures

Select first login only per day

RALL — Sat, 05 Sep 2020 03:14:03 GMT

I am trying to get the login data for users displayed per day.

So I want the first login only for that user and I want it per day (users log in multiple times per day and so I only want their first login for that day), I have been struggling to get it per day, any help would be much appreciated.

Re: Select first login only per day

mkeintz — Sat, 05 Sep 2020 04:35:33 GMT

@RALL wrote:

I am trying to get the login data for users displayed per day.

So I want the first login only for that user and I want it per day (users log in multiple times per day and so I only want their first login for that day), I have been struggling to get it per day, any help would be much appreciated.

Sample data please, in the form of a data step. Followed by the desired result, also in the form of a data step.

Help us help you.

Editted note: Let's assume your data are sorted chronologically, so each userid appears scattered throughout the data set. This is a case where the efficiency of hash object shines:

data _null_;
  if 0 then set have;
  declare hash h (dataset:'have',ordered:'A');
    h.definekey('userid');
    h.definedata(all:'Y');
    h.definedone();
  h.output(dataset:'want');
run;

This works because:

By default, the hash object (think lookup table) will keep only one dataitem (i.e. one record) per key (i.e. per userid).
Also by default, once a given key is in the hash it is NOT replaced when another record for the same userid is encountered. That is, it keeps the first instance of each userid.

The ordered:'A' parameter tells sas to maintain the hash object in ascending order by the key (userid). I.e. your want dataset will be sorted by userid, with one record per userid. This can be a good deal more efficient than sorting the original unreduced dataset file by userid.

Re: Select first login only per day

Kurt_Bremser — Sat, 05 Sep 2020 04:14:56 GMT

Sort by user, date and time, then run a data step with BY user date, and use a subsetting if first.date.

For detailed help, supply example data in a data step with datalines, against which we can write the code.

Re: Select first login only per day

RALL — Sat, 05 Sep 2020 05:34:03 GMT

Date	Time (UTC)	User
6/05/2020	03:57.9	GG
6/05/2020	03:57.4	DD
6/05/2020	03:57.2	AA
6/05/2020	03:57.1	HH
6/05/2020	03:57.0	AA
6/05/2020	03:56.8	II
6/05/2020	03:56.8	AA
6/05/2020	03:56.7	BB
6/05/2020	03:56.4	DD
6/05/2020	03:56.1	HH
6/05/2020	03:55.9	AA
6/05/2020	03:55.7	GG
6/05/2020	03:55.5	FF
6/05/2020	03:55.4	CC
6/05/2020	03:55.1	EE
6/05/2020	03:55.0	DD
6/05/2020	03:54.8	CC
6/05/2020	03:54.8	BB
6/05/2020	03:54.7	AA

Re: Select first login only per day

Jagadishkatam — Sat, 05 Sep 2020 05:43:32 GMT

please try the below code

proc sort data=have;
by user date time;
run;

data want;
set have;
by user date time;
if first.user;
run;

Re: Select first login only per day

Kurt_Bremser — Sat, 05 Sep 2020 06:03:24 GMT

Since you did not provide your data in a data step as requested, I have to assume that your date and time column are actually SAS dates and times.

So my previous suggestion turns into this code:

proc sort data=have;
by user date time;
run;

data want;
set have;
by user date;
if first.date;
run;

Re: Select first login only per day

RALL — Mon, 07 Sep 2020 02:24:42 GMT

Date (UTC)

User

Username

2020-08-31:20:46:49

I combined my date and time columns to make the column DATE (UTC) as above and ran the code as follows (they where separate date and time columns) ;

data ral.Sigins_dups_Removed1;
set ral.SIGNINS_COMBINED;
by username 'Date (UTC)'n;
if first.username;
run;

It is not giving me the the first sigin by day anymore, any idea what I need to change?