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

I have following table

Input  
S.No.DatetimeDetails
12010/6/7 19:01asd
12010/6/8 4:00dfg
22010/6/9 0:00dfg
22010/6/10 0:00gfd
22010/6/11 0:00gfd
32010/6/12 0:00gfd
32010/6/13 0:00abc
42010/6/14 0:00abc
42010/6/15 0:00def

 

and I am trying to get the following output

outputTimeDifference 1TimeDifference 2Detail 1Detail 2Detail 3
10.374305556 asddfg 
211dfggfdgfd
31 gfdabc 
41 abcdef 

 

I am using SAS Base 9.4. Till now I have tried Proc SQL, GroupBy and Proc Tabulate but its not working. Although I have not reached the stage to calculate time difference but I am thinking of using INTCK for the same. 

If somebody can think of any approach, please share. waiting for somebody to rescue me from this situation.

Thanks in advance !

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

The simplest way is IDGROUP. If you have big table ,Try MERGE Skill proposed by me,Arthur.T,Matt :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
infile cards expandtabs truncover;
input No	Datetime & anydtdtm.	Details $;
format Datetime datetime20.;
cards;
1	2010/6/7 19:01	asd
1	2010/6/8 4:00	dfg
2	2010/6/9 0:00	dfg
2	2010/6/10 0:00	gfd
2	2010/6/11 0:00	gfd
3	2010/6/12 0:00	gfd
3	2010/6/13 0:00	abc
4	2010/6/14 0:00	abc
4	2010/6/15 0:00	def
;
run;

data have;
 merge have have(firstobs=2 keep=No Datetime 
 rename=(No=_No Datetime=_Datetime));
 if No=_No then dif=divide(_Datetime-Datetime,'24:00:00't);
 drop _:;
run;
proc sql noprint;
select max(n) into : n separated by ' '
 from (select count(*) as n from have group by No);
quit;
proc summary data=have;
by No;
output out=want(drop=_: dif_&n) idgroup(out[&n] (dif Details)=);
run;

View solution in original post

10 REPLIES 10
PGStats
Opal | Level 21

INTCK is not meant to return fractional time interval counts. You could do this:

 

data td;
input SNo dt & :anydtdtm.	Details $;
format dt datetime.;
datalines;
1	2010/6/7 19:01  asd
1	2010/6/8 4:00  dfg
2	2010/6/9 0:00  dfg
2	2010/6/10 0:00  gfd
2	2010/6/11 0:00  gfd
3	2010/6/12 0:00  gfd
3	2010/6/13 0:00  abc
4	2010/6/14 0:00  abc
4	2010/6/15 0:00  def
;

proc sql;
select max(n), max(n)-1 into :nDt trimmed, :nDt1 trimmed
from (select count(*) as n from td group by SNo);
quit;

data want;
array detail_{&nDt} $8;
array TimeDifference_{&nDt1};
do i = 1 by 1 until(last.SNo);
    set td; by SNo;
    detail_{i} = details;
    if i > 1 then timeDifference_{i-1} = (dt - dt1) / '24:00:00't;
    dt1 = dt;
    end;
drop i dt dt1 details;
run;

proc print; run; 
PG
deega
Quartz | Level 8
@PG Stats@PG Stats
Thanks for this program.
Could you please tell the use/reason of using {&nDt} while defining arrays. In the basics of arrays all I understood is that we need to give the number of elements...
PGStats
Opal | Level 21

The SQL step defines two macro variables nDt and nDt1 that hold the maximum number of observations in a SNo group and that same number minus one, respectively. 

 

These macro values are used in the data step as array dimensions where &nDt and &nDt1 are mentioned.

PG
deega
Quartz | Level 8
@PG Stats@PG Stats
Thanks for the prompt reply.
I have written a program based on your program where I am not calculating Time Difference also. My Data set is around 15000000 records and program is running from last 2 hours, normally does it take this much time ?
PGStats
Opal | Level 21

That's not what I would expect if the program and the data are on the same machine.  But then, I haven't seen your code.

PG
Ksharp
Super User

The simplest way is IDGROUP. If you have big table ,Try MERGE Skill proposed by me,Arthur.T,Matt :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
infile cards expandtabs truncover;
input No	Datetime & anydtdtm.	Details $;
format Datetime datetime20.;
cards;
1	2010/6/7 19:01	asd
1	2010/6/8 4:00	dfg
2	2010/6/9 0:00	dfg
2	2010/6/10 0:00	gfd
2	2010/6/11 0:00	gfd
3	2010/6/12 0:00	gfd
3	2010/6/13 0:00	abc
4	2010/6/14 0:00	abc
4	2010/6/15 0:00	def
;
run;

data have;
 merge have have(firstobs=2 keep=No Datetime 
 rename=(No=_No Datetime=_Datetime));
 if No=_No then dif=divide(_Datetime-Datetime,'24:00:00't);
 drop _:;
run;
proc sql noprint;
select max(n) into : n separated by ' '
 from (select count(*) as n from have group by No);
quit;
proc summary data=have;
by No;
output out=want(drop=_: dif_&n) idgroup(out[&n] (dif Details)=);
run;
deega
Quartz | Level 8

Thanks for your prompt reply the other day. Now in the same problem I am facing a new issue. Actually I have big data in CSV format. When I import the data through SAS'S import wizard or proc import I get the dates in text format. Could you advise anything to get it in dates format ?

Ksharp
Super User

Copy the code generated by PROC IMPORT from LOG and Paste it into programm editor, Change that date variable's informat and see what happen ?

deega
Quartz | Level 8

Thank you so much for the suggestion but I deleted time from my datetime field in CSV file, after which I was able to import it as date. Since, now I dont have that old input so I can not try this option right now, may be some other time. 

Ksharp
Super User

Good news. Here are two choices:

 

1) Try some other option.

proc import .....;
scantime=yes;
usedate=yes;

 

2)Once import into SAS, use data step to transform it.

want_date=input(str_date,anydtdte.);

format want_date date9.;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 3099 views
  • 3 likes
  • 3 in conversation