I have following table
Input | ||
S.No. | Datetime | Details |
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 |
and I am trying to get the following output
output | TimeDifference 1 | TimeDifference 2 | Detail 1 | Detail 2 | Detail 3 |
1 | 0.374305556 | asd | dfg | ||
2 | 1 | 1 | dfg | gfd | gfd |
3 | 1 | gfd | abc | ||
4 | 1 | abc | def |
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 !
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;
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;
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.
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.
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;
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 ?
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 ?
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.
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.;
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.