SAS: Printing monthly and weekly average

Question

How can I print (and export to file) monthly and weekly average of value? The data is stored in a library and the form is following:

Obs.  Date                 Value 
1     08FEB2016:00:00:00   29.00 
2     05FEB2016:00:00:00   29.30 
3     04FEB2016:00:00:00   29.93 
4     03FEB2016:00:00:00   28.65 
5     02FEB2016:00:00:00   28.40 
(...)
3078  08MAR2004:00:00:00   32.59 
3079  05MAR2004:00:00:00   32.75 
3080  04MAR2004:00:00:00   32.05 
3081  03MAR2004:00:00:00   31.82

EDIT: I somehow managed to get the monthly data but I'm returning average for each month separately. I would to have it done as one result, namely Month-Average+export it to a file or a data set. And still I have no idea how to deal with weeks.

%macro printAvgM(start,end);
    proc summary data=sur1.dane(where=(Date>=&start
        and Date<=&end)) nway;
    var Value;
    output out=want (drop=_:) mean=;
    proc print;
    run;
%mend printAvgM;

%printAvgM('01jan2003'd,'31jan2003'd);

EDIT2: Here is my code, step by step:

libname sur 'C:\myPath';
run;

proc import datafile="C:\myPath\myData.csv"
out=SUR.DANE
dbms=csv replace;
getnames=yes;
run;

proc sort data=sur.dane out=sur.dane;
by Date;
run;

libname sur1 "C:\myPath\myDB.accdb";
run;

proc datasets;
copy in=sur out=sur1;
select dane;
run;

data sur1.dane2;
set sur1.dane;
date2=datepart(Date);
format date2 WEEKV11.;
run;

The last step results in NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. and the format of dane2 variable is DATETIME19..

Please post what you've tried so far. Look into proc means with a format such DT formats. — Reeza
Why are you writing your data to an access db, especially temp tables? Leave then in work - ie remove lib sur1 from last data step. — Reeza

Reeza Reeza · Accepted Answer · 2016-02-21T19:22:57

Ok, it's small enough to handle easily then. I would recommend first converting your datetime variable to a date variable using DATEPART() function and then use a format within PROC MEANS. You can look up the WEEKU and WEEKV formats to see if they meet your needs. The code below should be enough to get you started. You could do the monthly without the date conversion, but I couldn't find a weekly format for the datetime variable.

*Fake data generated;
data fd;
start=datetime();
do i=1 to 3000000 by 120;
    datetime=start+(i-1)*30;

    var=rand('normal', 25, 5);
    output;
end;
keep datetime var;
format datetime datetime21.;
run;

*Get date variable;
data fd_date;
set fd;
date_var = datepart(datetime);
date_month = put(date_var, yymon7,);
  Date_week = put(date_var, weekv11.);

  run;

*Monthly summary;
proc means data=fd_date noprint nway;
class date_var;
var var;
output out=want_monthly mean(var)=avg_var std(var)=std_var;
format date_var monyy7.;
run;

*Weekly summary;
proc means data=fd_date noprint nway;
class date_var;
var var;
output out=want_weekly mean(var)=avg_var std(var)=std_var;
format date_var weekv11.;
run;

Replace date_var with the new monthly and weekly variables. Because these are character variables they won't sort properly.

SAS: Printing monthly and weekly average

1 Answers