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
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;
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...
If you try this code?
Nope ..
DATE DISTNAME VAR1
19997 KCDRVA1A .
19997 KCDRVA16 .
19997 KCDRVA18 .
19997 RVDRVA15 .
19997 RVDRVA17 .
19997 RVDRVA19 .
Maybe the data-step works better (if it doesn't I'm out of luck; please make sure that the input-dataset is complete).
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.