Hi,
I have a (perhaps) rather simple question. I am working on real time data that looks like this:
Sample Location | Date | Time | Concentration X | Concentration Y |
---|---|---|---|---|
Office | 08/09/2009 | 1:00:00 | 2 | 7 |
Office | 08/09/2009 | 1:00:10 | 5 | 5 |
Office | 08/09/2009 | 1:00:30 | 5 | 6 |
There are several sampling locations, dates, and times. I would like to obtain average concentrations in these different areas, different dates (day of the week), and different times (morning, noon, evening, overnight).
I understand how to do this for sample location using proc means, but I get stumped when I need to do this for time and date variables.
I originally used proc means for different times of day but SAS did not understand this:
My questions are as follows:
1. Do I need to transform my 'Time' variable, because SAS does not seem to recognize this as time? If so, how do I do this. I was thinking of making a dummy variable for morning, noon, evening, overnight but SAS does not seem to recognize the time as it is in my database.
2. The same question but for Date. Are there any transformations that need to be made?
Thank-You.
The CARDS statement (you can also use DATALINES statement if you are too young to remember what a card punch is) tells SAS to treat the following lines as data rather than commands. I just copied your data from the original post. I copied it out of the HTML table and just pasted it as raw text. So the last two numbers on each line of data are your X and Y concentration values. SAS stops reading when it sees a semi-colon (;) on the line. You can also read from a text file by using an INFILE statement before the INPUT statement instead of the CARDS.
The INPUT statement tells it how to read the data. The first field is character, hence the $. The date and time fields have informats to tell SAS how to interpret the character strings as date and time. The : modifier tells it not to read too many characters. For example when the hour part of the time is less than 10 so that the time value only uses 7 characters instead of 8. The default informat is numeric so for the two concentration values I do not need to specify an informat.
The FORMAT statement tells SAS how to display the date and time variables so that humans can read them. I prefer to use Y/M/D order to display dates to avoid confusion between US default of M/D/Y and European default of D/M/Y. Also then the text string will sort in chronological order.
If your data is already in a dataset then you do not need to INPUT it. If you have it in a spreadsheet then you could try using PROC IMPORT to convert it to a dataset.
The INT(time/'06:00't) will divide the time (number of seconds since midnight) by the number of seconds in 6 hours and truncate to just the integer part. So you will get 0,1,2 or 3. I then added one so that the categories start from 1 instead of 0. To get them to display as you want you could use a format.
proc format ;
value tod 1='overnight' 2='morning' 3='afternoon' 4='evening' ;
run;
To create time of day categories as a character string using your time ranges you might just use IF/THEN logic. For example to create the categories as a character variable your could try:
if time < '06:00't then tod='overnight';
else if time <'12:00't then tod='morning';
else if time <'18:00't then tod='afternoon';
else tod='evening';
Your values look like times. In SAS a time value is a numeric variable with the number of seconds since midnight.
To create your dummy variables should be simple. You can use the WEEKDAY() function to convert a date to a day of the week (1=Sunday).
You can divide the day into what ever parts you want. In the example below I divide it into 6 hour windows.
data sample;
input location $ date :mmddyy10. time :time8. x y;
format date yymmdd10. time time8.;
dow=weekday(date);
tod=1+int(time/'06:00't);
cards;
Office 08/09/2009 1:00:00 2 7
Office 08/09/2009 9:00:10 5 5
Office 08/10/2009 13:00:30 5 6
run;
Thank you, Tom!
So, if you don't mind, can I break this code into digestible chunks?
data sample; (I understand this)
input location $ date :mmddyy10. time :time8. x y; (Are the next two lines of command formatting data into what SAS can read?)
format date yymmdd10. time time8.;
dow=weekday(date); (This give us a variable that codes our days of the week?)
tod=1+int(time/'06:00't); (This is our time of day variable, which tells us that we are going to break the day into 6 hour time intervals?)
cards; (I understand this)
The commands here I'm not following:
I don't understand what the below three lines are doing. It looks like it is defining when these 6 hours start and end?
Office 08/09/2009 1:00:00 2 7 (I'm also not sure what the numbers on the end mean--2, 7, or 5,5)
Office 08/09/2009 9:00:10 5 5
Office 08/10/2009 13:00:30 5 6 (Not sure why this switched to the 10th)
run;
So, my times of day are as follows:
morning (6am-12pm)
afternoon (12pm-6pm)
evening (6pm-12am),
overnight/night (12am-6am)
and sampling locations are office, dock, yard
So, would code be something like?
Office 08/09/2009 1:00:00 2 7
Office 08/09/2009 9:00:10 5 5
Office 08/10/2009 13:00:30 5 6
.
.
.Office 08/16/2009
Dock 08/09/2009 1:00:00 2 7
Dock 08/09/2009 9:00:10 5 5
Dock 08/10/2009 13:00:30 5 6
.
.
Dock 08/16/2009
.
.
.
run;
Sorry for so many questions. I'm new not very proficient in SAS. Thank you so much for your response.
The CARDS statement (you can also use DATALINES statement if you are too young to remember what a card punch is) tells SAS to treat the following lines as data rather than commands. I just copied your data from the original post. I copied it out of the HTML table and just pasted it as raw text. So the last two numbers on each line of data are your X and Y concentration values. SAS stops reading when it sees a semi-colon (;) on the line. You can also read from a text file by using an INFILE statement before the INPUT statement instead of the CARDS.
The INPUT statement tells it how to read the data. The first field is character, hence the $. The date and time fields have informats to tell SAS how to interpret the character strings as date and time. The : modifier tells it not to read too many characters. For example when the hour part of the time is less than 10 so that the time value only uses 7 characters instead of 8. The default informat is numeric so for the two concentration values I do not need to specify an informat.
The FORMAT statement tells SAS how to display the date and time variables so that humans can read them. I prefer to use Y/M/D order to display dates to avoid confusion between US default of M/D/Y and European default of D/M/Y. Also then the text string will sort in chronological order.
If your data is already in a dataset then you do not need to INPUT it. If you have it in a spreadsheet then you could try using PROC IMPORT to convert it to a dataset.
The INT(time/'06:00't) will divide the time (number of seconds since midnight) by the number of seconds in 6 hours and truncate to just the integer part. So you will get 0,1,2 or 3. I then added one so that the categories start from 1 instead of 0. To get them to display as you want you could use a format.
proc format ;
value tod 1='overnight' 2='morning' 3='afternoon' 4='evening' ;
run;
To create time of day categories as a character string using your time ranges you might just use IF/THEN logic. For example to create the categories as a character variable your could try:
if time < '06:00't then tod='overnight';
else if time <'12:00't then tod='morning';
else if time <'18:00't then tod='afternoon';
else tod='evening';
Tom,
Thank you SO much! I understand you completely. Thank you for taking time out of your Saturday to help a newbie like myself. Much appreciated!
Can I ask you one more question in regards to days of the week?
data sample;
input location $ date :mmddyy10. x y;
format date yymmdd10.;
dow=weekday(date);
will this code alone give me day of the week variable? How would I go from a date of 10/9/2009 to "Monday" (not sure if that is the correct date but just as an example)?
The WEEKDAY() function returns a number from 1 to 7. You could create your own format to display the names.
The DOWNAME format will display a date as the Sunday, Monday, etc.. You could use it with the PUT() function to generate a new character variable.
weekday=put(date,downame.);
Thank you so much, Tom, with your help, I figured it out and was able to build some code that works! You ROCK!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: