topic Re: Mean calculation based on 2nd data time range? in SAS Programming

Mean calculation based on 2nd data time range?

JeffNCSU — Wed, 06 Oct 2010 19:27:02 GMT

How to do data1 stat (for example mean) calculation based on data2 time range?

Data1

time x y
11:00 2 3
11:02 7 8
11:03 4 5
11:07 10 30
12:00 30 20
12:03 21 4
..............

Data2
TimeStart TimeEnd
11:00 11:07
11:03 12:00
12:00 12:03
....................

Want to get results
TimeStart TimeEnd MeanX meanY
11:00 11:07 ? ?
11:03 12:00 ? ?
12:00 12:03 ? ?
............................

Re: Mean calculation based on 2nd data time range?

Peter_C — Thu, 07 Oct 2010 07:08:54 GMT

Have a look at PROC SQL
Select starttime, endtime, mean(x) as meanX, mean(y) as meanY
from data1 a, data2 b
where a.time between starttime and endtime
Group by starttime, endtime

Well, something like that......

peterC
(not used to SAS-ing on iPhone standing on a crowded commuter train)

new defn of unstable code 😉
Needed group by
and "time" names reversed
and a new name for the mean() stat variables

Message was edited by: Peter.C

Re: Mean calculation based on 2nd data time range?

JeffNCSU — Thu, 07 Oct 2010 13:00:07 GMT

Thanks! PeterC,

Could you please give me an example? I am not familar with Proc SQL.

Re: Mean calculation based on 2nd data time range?

sbb — Thu, 07 Oct 2010 14:23:43 GMT

There is much information on the SAS support website, with technical reference, conference papers, and SAS-hosted documentation. Suggest using the SEARCH facility there or consider a Google advanced search argument, such as:

proc sql example site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Mean calculation based on 2nd data time range?

chang_y_chung_hotmail_com — Thu, 07 Oct 2010 18:17:27 GMT

This would have been easier if the time ranges did not overlap. Having no overlaps would have allowed us to utilize various table look-up techniques like formats, hash tables, or arrays.

My macro solution is unnecessarily complicated and inefficient. See Joe's or data _null_'s.

(my code is little too long for this forum. I have posted the code on sas-l:
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010a&L=sas-l&D=1&O=D&P=43780 )

Look at Joe Matise's succinct proc sql solution at:
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010a&L=sas-l&D=1&O=D&P=44045

Or data _null_'s using the multi-label format:
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1010a&L=sas-l&D=1&O=D&F=&S=&P=44173

Re: Mean calculation based on 2nd data time range?

Peter_C — Fri, 08 Oct 2010 11:54:40 GMT

JeffNcsu

sorry you didn't understand:
examples you can find in the manual
examples you can find in online-doc

what you got with my posting was the sql you need
(apart from the top and tail
proc sql ;
create table results as

Select TimeStart, TimeEnd, mean(x) as meanX, mean(y) as meanY
from data1 a, data2 b
where a.time between TimeStart and TimeEnd
Group by TimeStart, TimeEnd

;
quit ;
That produced these work.RESULTS, based on your sample data[pre]+FSVIEW: WORK.RESULTS (B)-------------------------------+
| Obs TimeStart TimeEnd meanX meanY |
| |
| 1 11:00 11:07 5.75 11.5 |
| 2 11:03 12:00 14.666666667 18.333333333 |
| 3 12:00 12:03 25.5 12 |
| |
+--------------------------------------------------------+[/pre]since these are the results you seemed to be asking for, it is a bit disappointed you didn't try

peterC