/******************************************************************************************************************** * TemperatureSummary_SASMacro.sas * * Author: Gwynne Chandler, Fishery Biologist * * USFS Rocky Mountain Research Station * * Boise Aquatic Sciences Lab * * Boise, ID 83702 * * email: glchandler@fs.fed.us * * * * This is a very basic level program used to summarize temperature data collected with automatic temperature * * recorders. We truncate the data to the day after the instrument was deployed in the stream and the day before * * it was removed from the stream. When deploying the instrument we leave the filename as the default the * * would give it (for example, Onset devices use the serial number of the recording instrument). We record the * * and time the device was placed in the water as well as the stream name and description of the site (usually a * * gps coordinate is also recorded). This is the minimal level of data needed to summarize temperature. When the * * device is retrieved, we not the date and time of retrieval as well as the condition of the device (i.e. whether * * it was out of the water or appeared to have been disturbed). The data is then downloaded into the following * * format: SiteID - Unique identifier for the sample site * * sampDate - Date of sample in a date format * * sampTime - Time of sample in a time format * * Temp - temperature recorded by the device * * This data is saved into an Access database. This routine can be edited to import many different formats such * * .xls, .csv, .txt, and .dbf files. Once the data is imported two summaries are created: a daily mean summary * * and on overall summary for the time period of sampling. At this time the daily metrics output are: mean, max, * * min, standard deviation, range, moving 7-day mean (awat), and moving 7-day mean maximum (awmt). The overall * * metrics that are output are: mean, max, overall mean of average weekly mean (awat_mn), overall mean of * * average weekly maximum (awmt_mn), overall maximum of average weekly mean (mwat), overall maximum of average * * weekly maximum (mwmt). The overall summary also gives the start date and end date of the sampling period. * * The data is then exported back to the Access database as new tables. * * * * This routine is meant to be flexible and as it evolves we can add more metrics, more options for importing and * * exporting and other suggestions made by users. If there are any questions for its use or there is an error in * * code please contact Gwynne Chandler using the above email. * * This routine does require a SAS license and a basic knowledge of the SAS software. * * IF using this with Access, note that the Access database that houses the data must be closed while this is * * running. * ********************************************************************************************************************/ PROC IMPORT OUT= RawTemp /* name of datafile to be used initially in this routine - DO NOT CHANGE THIS NAME */ DATATABLE= "RawData" /* Within the quotes place the name of datatable in Access that has the temperature data */ DBMS=ACCESS2000 REPLACE; DATABASE="C:\Documents and Settings\glchandler\My Documents\Data\TemperatureData\Water\SASMacroData"; /* Within the quotes place the path and filename of access database */ /* NOTE: The pathname and database file have to match in this import statement and in the Export statements that are at the end of this routine. There has to be a database already created for SAS to import from and export to */ /********************************************************************************************************************** ** DO NOT CHANGE ANYTHING FROM THIS POINT TO THE EXPORT STATEMENT ** **********************************************************************************************************************/ DATA SRawTemp; FORMAT Date date9.; FORMAT Time time8.; SET RawTemp; Date=DatePart(sampDate); Time=Timepart(sampTime); Month=Month(Date); Day=Day(Date); Year=Year(Date); DROP sampDate sampTime; PROC SORT; BY SiteID date; PROC MEANS N MEAN SUM MIN MAX RANGE STD NOPRINT; BY SiteID date; VAR Temp Year; OUTPUT OUT=DailyMean N=Nobs_Day MEAN=Mean_d Year MAX=Max_d MIN=Min_d RANGE=Range_d STD=STD_d; DATA two; SET DailyMean; LABEL NOBS_DAY='Number of daily samples'; LABEL Mean_d='Mean Daily Temperature'; LABEL Max_d='Maximum Daily Temperature'; LABEL Min_d='Minimum Daily Temperature'; LABEL Range_d='Daily Range in Temperature'; LABEL STD_d='Daily Standard Deviation of Temperature'; PROC SORT; BY Siteid Year date; DATA DailySummary; SET two; BY SiteID Year; IF first.siteid + first.year THEN z=0; z+1; AWAT=(mean_d + lag1(mean_d) + lag2(mean_d) + lag3(mean_d) + lag4(mean_d) + lag5(mean_d) + lag6(mean_d))/7; AWMT=(max_d + lag1(max_d) + lag2(max_d) + lag3(max_d) + lag4(max_d) + lag5(max_d) + lag6(max_d))/7; IF z<7 THEN DO; awat=.; awmt=.; END; LABEL awat='Moving 7-day Mean Temperature'; LABEL awmt='Moving 7-day Mean Maximum Temperature'; DROP z _type_ _freq_; proc sort; by siteid year date; PROC MEANS N MEAN MIN MAX SUM NOPRINT; BY siteid year; VAR date nobs_day mean_d max_d awat awmt; OUTPUT OUT=OverallSummary N=junk1 N_Days MEAN=junk2 Nobs_day Overall_mn junk3 awat_mn awmt_mn MIN=StartDate MAX=EndDate junk4 junk5 Overall_mx Mwat Mwmt; DATA SUMMARY; SET OverallSummary; FORMAT AWAT_MN 6.2; FORMAT AWMT_MN 6.2; FORMAT MWAT 6.2; FORMAT MWMT 6.2; LABEL N_Days='Number of days sampled'; LABEL Summer_mn='Overall mean temperature'; LABEL awat_mn='Overall mean of average weekly mean temperature'; LABEL awmt_mn='Overall mean of average weekly maximum temperature'; LABEL Summer_mx='Overall maximum temperature'; LABEL Mwat='Overall maximum of average weekly mean temperature'; LABEL Mwmt='Overall maximum of average weekly maximum temperature'; DROP junk1-junk5 _type_ _freq_; Proc Sort; by SiteID; /******************************************************************************************************************** ** The pathway and filename in this Export statement (DATABASE =) must be the same as the import statement **** ** DO NOT CHANGE anything except the DATABASE= statement **** ********************************************************************************************************************/ PROC EXPORT DATA= DailySummary OUTTABLE= "tblDailySummary" DBMS=ACCESS2000 REPLACE; DATABASE="C:\Documents and Settings\glchandler\My Documents\Data\TemperatureData\Water\SASMacroData"; PROC EXPORT DATA= Summary OUTTABLE= "tblOverallSummary" DBMS=ACCESS2000 REPLACE; DATABASE="C:\Documents and Settings\glchandler\My Documents\Data\TemperatureData\Water\SASMacroData"; RUN;