## Sum across month date and recurring item_code

Solved
Occasional Contributor
Posts: 15

# Sum across month date and recurring item_code

Many thanks, this works, now my problem is that I want to sum the number of prescriptions in each month by item_code

That is, there should be a final table that has columns period, item_code and prescriptions.

There should be rows of which the first rown is the column name, the second should be 01SEP2013 and the third should be 01OCT2013.

The observations in the cells should be the sum of prescriptions in each month by (unique) item_code

 item_code sum of prescriptions 1-Sep-13 1-Oct-13

Proc sql;

create table homebase.dummy3 as

select item_code, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

Accepted Solutions
Solution
‎08-04-2017 02:44 AM
PROC Star
Posts: 2,321

## Re: Sum across month date and recurring item_code

Proc sql;

create table homebase.dummy3 as

select item_code, period, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

All Replies
Occasional Contributor
Posts: 15

## Re: Sum across month date and recurring item_code

Sorry, I mean the following should be the result with the last column being the sum of prescriptions:

 item_code period (No column name) 03387G 9/01/2013 14214 03387G 10/01/2013 3146 03390K 9/01/2013 91 03390K 10/01/2013 325567 03391L 9/01/2013 7 03391L 10/01/2013 44889 03393N 9/01/2013 867 03393N 10/01/2013 52
Solution
‎08-04-2017 02:44 AM
PROC Star
Posts: 2,321

## Re: Sum across month date and recurring item_code

Proc sql;

create table homebase.dummy3 as

select item_code, period, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

Occasional Contributor
Posts: 15

## Re: Sum across month date and recurring item_code

thanks for replying Chris. I ran the code but realise now there is something wrong in an earlier step where i create a precursor table called dummy2 and when I proc print the table it looks fine. However when I open it, the message I get is Data set has nil observations.

I've attached my original data which is imported to SAS as dummy1 which then is converted to dummy2 (to make the date column SAS based) and then dummy3 to try and do the sum of prescriptions

Here's my code:

Libname pointing to homebase folder;

libname homebase 'C:\Homebase';

* View SAS table called dummy1 ;

proc print data=homebase.dummy1;

run;

* this creates a new table and formats the period column to be a SAS DATE column;

data homebase.dummy2;

set homebase.dummy1;

input period : ddmmyy10. (item_code patient_cat prescriptions) (\$);

format period yymmd.;

datalines;

run;

proc print data=homebase.dummy2;

run;

Proc sql;

create table homebase.dummy3 as

select item_code, period, sum(prescriptions) as prescriptions_sum

from homebase.dummy2

group by item_code, period;

quit;

proc print data=homebase.dummy3;

run;

Super User
Posts: 9,599

## Re: Sum across month date and recurring item_code

Post your test data in the form of a datastep using a code window - its the {i} above the post area.  Office files are not safe to download off the net.

PROC Star
Posts: 2,321

## Re: Sum across month date and recurring item_code

You shouldn't have a set statement (read a sas table) and input+datalines (read free text) statement together.

I suspect you want set.

☑ This topic is solved.