topic Re: Average of time per day by variable in SAS Procedures

Average of time per day by variable

rkumar23 — Wed, 19 Nov 2014 05:55:20 GMT

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

Re: Average of time per day by variable

user24feb — Wed, 19 Nov 2014 07:05:54 GMT

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;

Re: Average of time per day by variable

rkumar23 — Wed, 19 Nov 2014 07:36:50 GMT

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

Re: Average of time per day by variable

user24feb — Wed, 19 Nov 2014 07:52:44 GMT

If you try this code?

Re: Average of time per day by variable

rkumar23 — Wed, 19 Nov 2014 08:22:40 GMT

Nope ..

DATE DISTNAME VAR1

19997 KCDRVA1A .

19997 KCDRVA16 .

19997 KCDRVA18 .

19997 RVDRVA15 .

19997 RVDRVA17 .

19997 RVDRVA19 .

Re: Average of time per day by variable

user24feb — Wed, 19 Nov 2014 08:47:50 GMT

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

Re: Average of time per day by variable

RW9 — Wed, 19 Nov 2014 09:42:12 GMT

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;