SAS Sum daily date into weeks

user_16817489 0
Viewed 241

I am trying to take daily data (not every day has data) and sum it by weeks starting on monday. Below is a very small sample for the Data I have. the code is irrelvant for the summing purposes needed here.

Data have:

ID PKG Revdt code QTY
70 17AB 02AUG2021:00:00:00 01 7
70 17AB 04AUG2021:00:00:00 02 -10
70 17AB 05AUG2021:00:00:00 01 8
70 17AB 10AUG2021:00:00:00 01 7
70 17AB 1QAUG2021:00:00:00 01 7
73 12AC 02AUG2021:00:00:00 09 0
73 17AC 07AUG2021:00:00:00 01 7

Data want

ID PKG Revdt code QTY
70 17AB 02AUG2021:00:00:00 01 5
70 17AB 09AUG2021:00:00:00 01 14
73 12AC 02AUG2021:00:00:00 01 7

I have tried the below

Proc sql;
    connect to odbc (dsn='' id='' p='');
    create table work.WeeklySum as select distinct * from connection to odbc
         (select ID, Pkg, datepart(week, revdt), code, sum(qty)
             from datebase
             group by datepart(week, revdt) );
disconnect from odbc;
quit;

However when i run it, it says "error:proc sql requires any created table to have atleast 1 column"

sas cumulative-sum aggregate date sql-server
2 Answers

I would just pull the data with the daily rows first into SAS. If it is too big, write a macro to run it in loops with small chunks.

Once you get the daily dataset into SAS, create a separate table with weekly datetimes (here I am creating a table begins with Jan 1, 2021 and has a row for every week from there - change the inputs according to your need):

data weeks;
format week_date datetime19.;
    do i = 0 to 52; 
    week_date = intnx('dtday', "01jan2021:00:00:00"dt, 7*i,'s');
    output;
    end;
drop i;
run;

Once you have this weeks dataset, left join your daily dataset to it using:

week_date <= revdt < intnx('dtday', week_date, 7, 's')

and then sum your variable by week_date.

user_168181110 
select ID, Pkg, datepart(week, revdt), code, sum(qty)
             from datebase
             group by datepart(week, revdt)

This is not valid in SQL Server, so this will be your problem. SAS lets you do this, but when you do pass-through SQL, you have to follow their rules. In the case of SQL Server, every variable that is not part of a summary function (here, sum(qty) is the only summary function call) has to be part of the group by.

If you want to group by inside SQL Server, you'll have to modify your query to be a join from the (ID,Pkg) query to the (datepart,sum) query. SAS does this for you, but SQL Server won't.

user_16230070
Related