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