DATA Step, Macro, Functions and more

sas query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

sas query

Dear All,

 

Can some one help with edit check. I have dataset with three collumns ID, DATE, TIME.

 

I have to write a program to confirm that  assessment time between two measurements should be at least 2 minute apart.I have to output the discripancies where the assessments are performed within 2minutes.

 

Below is the sample data.

 

10031001 28SEP2015 08:36

10031001 28SEP2015 08:38

10031001 28SEP2015 08:39

10031001 28SEP2015 08:40

10031001 28SEP2015 08:42

10031001 28SEP2015 08:44

10031001 28SEP2015 08:46

 10031002 28OCT2015 08:36

10031002 28OCT2015 08:39

10031002 28OCT2015 08:40

10031002 30OCT2015 08:40

10031002 30OCT2015 08:41

10031002 30OCT2015 08:43

10031002 30OCT2015 08:45

 

The output shoudl look like this

 

10031001 28SEP2015 08:38

10031001 28SEP2015 08:39

10031001 28SEP2015 08:40

10031002 28OCT2015 08:39

10031002 28OCT2015 08:40

10031002 30OCT2015 08:40

10031002 30OCT2015 08:41

 

Thanks in advance

Rakesh


Accepted Solutions
Solution
‎03-06-2017 02:59 PM
PROC Star
Posts: 7,363

Re: sas query

Since you have to look ahead and back, I'd use something like the following:

 

data have;
  informat date date9.;
  informat time time5.;
  input ID date time;
  format date date9.;
  format time time5.;
  cards;
10031001 28SEP2015 08:36
10031001 28SEP2015 08:38
10031001 28SEP2015 08:39
10031001 28SEP2015 08:40
10031001 28SEP2015 08:42
10031001 28SEP2015 08:44
10031001 28SEP2015 08:46
10031002 28OCT2015 08:36
10031002 28OCT2015 08:39
10031002 28OCT2015 08:40
10031002 30OCT2015 08:40
10031002 30OCT2015 08:41
10031002 30OCT2015 08:43
10031002 30OCT2015 08:45
;

data have;
  set have;
  dt=input(catx(':',put(date,date9.),put(time,time5.)),anydtdtm15.);
  format dt datetime21.;
run;

data want (keep=id date time);
  set have;
  by ID;
  set have ( firstobs = 2 keep = dt rename = (dt = Next_dt) )
      have (      obs = 1 drop = _all_                      );
  Prev_dt = ifn( first.ID, (.), lag(dt) );
  Next_dt = ifn(  last.ID, (.), Next_dt );
  if not missing(Next_dt) and (Next_dt-dt) lt 120 then output;
  else if not missing(Prev_dt) and (dt-Prev_dt) lt 120 then output;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Super User
Posts: 17,828

Re: sas query

Look at DIF() function. 

If this is a check post your current code. 

Solution
‎03-06-2017 02:59 PM
PROC Star
Posts: 7,363

Re: sas query

Since you have to look ahead and back, I'd use something like the following:

 

data have;
  informat date date9.;
  informat time time5.;
  input ID date time;
  format date date9.;
  format time time5.;
  cards;
10031001 28SEP2015 08:36
10031001 28SEP2015 08:38
10031001 28SEP2015 08:39
10031001 28SEP2015 08:40
10031001 28SEP2015 08:42
10031001 28SEP2015 08:44
10031001 28SEP2015 08:46
10031002 28OCT2015 08:36
10031002 28OCT2015 08:39
10031002 28OCT2015 08:40
10031002 30OCT2015 08:40
10031002 30OCT2015 08:41
10031002 30OCT2015 08:43
10031002 30OCT2015 08:45
;

data have;
  set have;
  dt=input(catx(':',put(date,date9.),put(time,time5.)),anydtdtm15.);
  format dt datetime21.;
run;

data want (keep=id date time);
  set have;
  by ID;
  set have ( firstobs = 2 keep = dt rename = (dt = Next_dt) )
      have (      obs = 1 drop = _all_                      );
  Prev_dt = ifn( first.ID, (.), lag(dt) );
  Next_dt = ifn(  last.ID, (.), Next_dt );
  if not missing(Next_dt) and (Next_dt-dt) lt 120 then output;
  else if not missing(Prev_dt) and (dt-Prev_dt) lt 120 then output;
run;

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 145

Re: sas query

Thank you Sir for the quick reply......

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 166 views
  • 1 like
  • 3 in conversation