## Average of time per day by variable

Frequent Contributor
Posts: 79

# Average of time per day by variable

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

Super Contributor
Posts: 355

## Re: Average of time per day by variable

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;

Frequent Contributor
Posts: 79

## Re: Average of time per day by variable

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

Super Contributor
Posts: 355

## Re: Average of time per day by variable

If you try this code?

Frequent Contributor
Posts: 79

## Re: Average of time per day by variable

Nope ..

DATE    DISTNAME    VAR1

19997    KCDRVA1A       .

19997    KCDRVA16       .

19997    KCDRVA18       .

19997    RVDRVA15       .

19997    RVDRVA17       .

19997    RVDRVA19       .

Super Contributor
Posts: 355

## Re: Average of time per day by variable

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

Super User
Posts: 9,599

## Re: Average of time per day by variable

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;

Discussion stats
• 6 replies
• 754 views
• 0 likes
• 3 in conversation