Average of time per day by variable

[rkumar23]

I have a month of data as below And I need to calculate the Average on VAR1 per day(date) by DISTNAME .....any thought?

DATE       TIME    X      Y     Z         DISTNAME     VAR1    THRESHOLD

01OCT14  0:00 10000  04  10015  RVDRVA15      0:00     1:00

01OCT14  0:00 10000  02  10018  KCDRVA18      0:02     1:00

01OCT14  0:00 10000  05  10016  KCDRVA16      0:01     1:00

01OCT14  0:00 10000  00  10017  RVDRVA17      0:00     1:00

01OCT14  0:00 10000  03  1001A  KCDRVA1A      0:02     1:00

01OCT14  0:00 10000  01  10019  RVDRVA19      0:00     1:00

01OCT14  0:15 10000  04  10015  RVDRVA15      0:00     1:00

01OCT14  0:15 10000  02  10018  KCDRVA18      0:04     1:00

01OCT14  0:15 10000  05  10016  KCDRVA16      0:02     1:00

01OCT14  0:15 10000  00  10017  RVDRVA17      0:00     1:00

01OCT14  0:15 10000  03  1001A  KCDRVA1A      0:04     1:00

01OCT14  0:15 10000  01  10019  RVDRVA19      0:00     1:00

01OCT14  0:30 10000  04  10015  RVDRVA15      0:00     1:00

01OCT14  0:30 10000  02  10018  KCDRVA18      0:02     1:00

01OCT14  0:30 10000  05  10016  KCDRVA16      0:01     1:00

01OCT14  0:30 10000  00  10017  RVDRVA17      0:00     1:00

[user24feb]

If it is just about the average: (?)

Proc Means Data=Your_Set NWay NoPrint;

  Class Date DistName;

  Var Var1;

  Output Out=Want (Drop=_:) Mean=;

Run;

[rkumar23]

It doesn't seems to bring write output....Do you know what should be the format for VAR1 while Reading? as if read with TIME. format that make output/average as 0:

  DAT      DISTNAME    DEFERR   

01OCT14    KCDRVA1A       0     

01OCT14    KCDRVA16       0     

01OCT14    KCDRVA18       0     

01OCT14    RVDRVA15       0     

01OCT14    RVDRVA17       0     

01OCT14    RVDRVA19       0    

While reading VAR1 as numeric data bring missing value...

[user24feb]

If you try this code?

[rkumar23]

Nope ..

DATE    DISTNAME    VAR1

19997    KCDRVA1A       .      

19997    KCDRVA16       .      

19997    KCDRVA18       .      

19997    RVDRVA15       .      

19997    RVDRVA17       .      

19997    RVDRVA19       .     

[user24feb]

Maybe the data-step works better (if it doesn't I'm out of luck; please make sure that the input-dataset is complete).

[RW9]

What is the format of VAR1, it looks like a time format?  If so then convert it to number.  Then you just need a sum of ROWS by DATE, and a count of ROWS by date:

data have;

  date="01OCT2014"d; var1="0:00"t; output;

  date="01OCT2014"d; var1="0:02"t; output;

  date="01OCT2014"d; var1="0:01"t; output;

  date="01OCT2014"d; var1="0:00"t; output;

  date="01OCT2014"d; var1="0:02"t; output;

run;

proc sql;

  create table WANT as

  select  distinct

          A.DATE format=date9.,

          B.ROW_SUM,

          C.CONVERTED / B.ROW_SUM as AV

  from    WORK.HAVE A

  left join (select distinct DATE,count(DATE) as ROW_SUM from WORK.HAVE group by DATE) B

  on      A.DATE=B.DATE

  left join (select DATE,sum(input(compress(tranwrd(put(VAR1,time5.),":","")),best.)) as CONVERTED from WORK.HAVE group by DATE) C

  on      A.DATE=C.DATE;

quit;