Time intervals. HELP!!

[deleted_user]

Hello,

I am a new SAS user. I am still trying to understand how it works. I finally figured out how to import data from excel. It reads fine, but now I do not know how to get the averages of a certain variable in a specific time interval.

IMy columns go like this

var1 date time var 2 var 3... etc

What I want to do is to get the average in a specific date and in a specific time frame. It is not clear for me where to write the command, how to use it or which command use to get that.

Each day has a different time interval.

So far I have:



Proc import datafile = 'C:\Users\monicam\Desktop\Temp_PAR_SAS\All_2007.xls'
out=All
DBMS=excel
replace;
GETNAMES=yes;
USEDATE=yes;
SCANTIME=yes;

RUN;


I am sorry if this is such an easy question but I have been using the program for a month or so.

I just need to know how to tell sas to read the time and date. I think after that I can use a "where" statement and I can put the dates and times needed. Or maybe an if statement?

So far the only thing that I have been doing is working with small sets that I have been copying and pasting in the editor screen. this importing businesses is complicated for me.

Any help will be greatly appreciated.

Monica

[deleted_user]

Hi Monica and welcome to SAS!

If I'm understanding your question correctly, you are trying to get an average of say, var1, in a specific date and time range. It looks like your date and time fields are separate. Is that correct? If you make the date and time 1 field, this is the format you would use in a where statement if querying:

Datetime Constant Time
'17OCT1991:14:45:32'DT 32 seconds past 2:45 p.m., 17 October 1991
'17OCT1991:12:5'DT 12:05 p.m., 17 October 1991
'17OCT1991:2:0'DT 2 AM, 17 October 1991
'17OCT1991:0:0'DT midnight, 17 October 1991

I'm sure others might have a better suggestion, but I would simply do:

proc sql;
create table work.x as
select
avg(var1)
from
work.y
where
datetime between '17OCT1991:14:45:32'DT and '18OCT1991:14:45:32'DT;
quit;

You'll have to tweak this, but maybe this will help?

Also, this link is a great reference page to use - it's helped me a lot!
http://support.sas.com/onlinedoc/913/docMainpage.jsp

[deleted_user]

Thanks I will try that. I wish I knew that before... I changed the format (it was just in one column) I thought it was going to be easier. It took me 2 days to do that...

[deleted_user]

I think I found a shortcut. However now I need more help... What I did was:

proc means data= all;
by sun_shade;
where date eq 17331 and time gt 29590 and time lt 30000;
run;
quit;


However, now I need a fastest way to know the number that corresponds to the date and time without having to go to analyst every time. Is there a formula that I can apply.

[Cynthia_sas]

Hi:
You -could- use the internal number, in your WHERE statement, but there is something called a DATE constant, which is an alternate way to ask for dates and a TIME constant method to ask for time values -- all without knowing the internal numbers.

Let's start simply. November 15, 1950 was 3334 days before Jan 1, 1960 (which is the SAS start date or 0 date. So internally, the number for 11/15/50 is -3334. Similarly, Nov 29, 1984 was 9099 days after Jan 1, 1960. But in order to use either of those dates in a WHERE statement, I only have to know how to specify a DATE constant in the WHERE clause:
[pre]
where datevar = '15NOV50'd;
where datevar = "29NOV1984"d;
where datevar = '15NOV1950'd and timevar gt '19:00't;
[/pre]
would all work. The date is in quotes, immediately followed by the letter D -- this construction tells SAS to convert the readable date to an internal date value for purposes of the comparison. (You can use either single or double quotes around the readable date -- BUT the date has to be in the form DDMMMYY or DDMMMYYYY)

There are similar constants for time (the internal time value represents the number of seconds since midnight of the current day. SAS time values are between 0 and 86400 = 60 sec per min * 60 min per hour * 24 hour per day) So the time value of 19:35 is 70500 seconds since midnight. But notice how I simplify that in the WHERE statement by asking for TIMEVAR GT '19:00't

I ran a SAS program to find out those internal date and time values (-3334, 9099, etc), but I don't really care how SAS internally stores my date and time values, as long as I can supply a readable date and readable time to get observations I want.

See the programs at the bottom of this post. Note that the FORMAT statement isn't changing the internal storage of the DATEVAR or the TIMEVAR -- it is merely displaying those values in a way that I can read without counting the number of days and/or number of seconds on my fingers and toes.

I showed the WHERE statements with PROC PRINT, but you could just as easily use them with PROC MEANS.

Just like there are DATE constants and TIME constants, there are DATETIME constants (like when you get data that is time stamped and the date and time are held in 1 variable instead of 2). There are also HEX constants and BINARY constants. If you search the documentation for a topic called, "Expressions: SAS Constants in Expressions" you will find the entire list of constants and examples of usage.

cynthia
[pre]
** make some data;
data testdate;
infile datalines;
input var1 $ datevar : mmddyy10. timevar time5. var2 var3;
return;
datalines;
aaa 11/15/1950 19:35 101 202
bbb 11/15/1950 09:42 111 222
ccc 10/17/1991 14:45 333 444
ddd 11/29/1984 10:36 555 666
;
run;

options nodate nonumber nocenter;
ods listing;
** print data WITHOUT formats to show internal values;
proc print data=testdate;
title '1) no formats show internal values';
run;

** print data WITH formats to show readable values;
proc print data=testdate;
title '2) with formats display readable values';
format datevar date7. timevar time5.;
run;

** Use an INTERNAL value in a WHERE statement;
proc print data=testdate;
title '3) selecting with WHERE logic using internal number';
where datevar = -3334;
format datevar date9. timevar time5.;
run;

** Use a DATE Constant in a WHERE statement;
proc print data=testdate;
title '4) Use CONSTANT value for WHERE';
where datevar = '15NOV1950'd;
format datevar mmddyy10. timevar time5.;
run;

** Use a DATE and TIME constant in a WHERE statement.;
proc print data=testdate;
title '5) Use CONSTANT (date, time) value for WHERE';
where datevar = '15NOV1950'd and timevar gt '19:00't;
format datevar worddate. timevar time5.;
run;

[/pre]

[deleted_user]

Thank you!! I feel that I still have long ways to go to be fluent in this language...