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

 

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
  • 10 replies
  • 1553 views
  • 3 likes
  • 3 in conversation